Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Probem with dynamic SQL on DW then a retrieve - 1 error (PB 8)

7 posts in DataWindow Last posting was on 2010-01-18 23:06:05.0Z
gooddawggy Posted on 2010-01-07 22:21:57.0Z
From: gooddawggy <gooddawggy@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Probem with dynamic SQL on DW then a retrieve - 1 error (PB 8)
Date: Thu, 7 Jan 2010 14:21:57 -0800 (PST)
Organization: http://groups.google.com
Lines: 65
Message-ID: <7e38b4ea-b699-4ed7-a872-f3b62163dcb8@p8g2000yqb.googlegroups.com>
NNTP-Posting-Host: 12.163.127.241
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1262902918 10971 127.0.0.1 (7 Jan 2010 22:21:58 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 7 Jan 2010 22:21:58 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: p8g2000yqb.googlegroups.com; posting-host=12.163.127.241; posting-account=QgXFPQoAAAB84vWxq6gT-rvod2LNGaSK
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/3.0.195.38 Safari/532.0,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!newsfeed.news2me.com!nx02.iad01.newshosting.com!newshosting.com!novia!news-out.readnews.com!transit4.readnews.com!postnews.google.com!p8g2000yqb.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90553
Article PK: 410159

I am trying to design a large report that the user can select fields
and build out the report depending on what they need.

The problem that I am having is that I modify the default datastore
with the new SQL. When I capture the SQL it runs fine pulling
directly from the database. So the sql builds out fine.

I capture the return on the SetSQLSelect() and it says that its
modifying the SQL on the datastore just fine but its not. (no -1
return)

I try to then retrieve the datastore and I get a -1 on retrieve. Not
really an intuitive error code. The transaction object doesn't help
very much either. No error there.

I have tried re-connecting the datastore to the trans object after the
SQL modification as well but that didn't work either.

Also tried destroying the datastore and then re-doing it and modifying
it but that didn't work.

This is what the SQL is being modified from:

SELECT active_dental_status=cli_clients_only.active_dental_status,
consultant=cli_clients_only.consultant,
code_id=cli_clients_only.code_id, Mnumber=cli_clients_only.old_acct,
Client_name=cli_clients_only.sort_by
FROM cli_clients_only
LEFT JOIN cli_iar_summary ON cli_clients_only.code_id =
cli_iar_summary.client_code_id_link
LEFT JOIN cli_contacts_cli contact ON cli_clients_only.code_id =
contact.client_code_id
LEFT JOIN cli_mkt_info ON cli_clients_only.code_id =
cli_mkt_info.client_code_id_link
LEFT JOIN cli_retainer_info ON cli_clients_only.code_id =
cli_retainer_info.client_code_id_link
LEFT JOIN cli_goals_meeting_master cgmm ON cli_clients_only.code_id =
cgmm.code_id_link
AND cgmm.contact_type = '360'
WHERE cli_clients_only.consultant = 'Consultant'

This is what it is being modified to (there is only one column
addition in the SELECT portion)
SELECT active_dental_status=cli_clients_only.active_dental_status,
consultant=cli_clients_only.consultant,
code_id=cli_clients_only.code_id, Mnumber=cli_clients_only.old_acct,
Client_name=cli_clients_only.sort_by
, cli_clients_only.pendingNLC FROM cli_clients_only
LEFT JOIN cli_iar_summary ON cli_clients_only.code_id =
cli_iar_summary.client_code_id_link
LEFT JOIN cli_contacts_cli contact ON cli_clients_only.code_id =
contact.client_code_id
LEFT JOIN cli_mkt_info ON cli_clients_only.code_id =
cli_mkt_info.client_code_id_link
LEFT JOIN cli_retainer_info ON cli_clients_only.code_id =
cli_retainer_info.client_code_id_link
LEFT JOIN cli_goals_meeting_master cgmm ON cli_clients_only.code_id =
cgmm.code_id_link
AND cgmm.contact_type = '360'
WHERE cli_clients_only.consultant = 'Consultant'

Any help with this problem would be much appreciated.

Thanks


gooddawggy Posted on 2010-01-07 22:47:06.0Z
From: gooddawggy <gooddawggy@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Probem with dynamic SQL on DW then a retrieve - 1 error (PB 8)
Date: Thu, 7 Jan 2010 14:47:06 -0800 (PST)
Organization: http://groups.google.com
Lines: 79
Message-ID: <44d1e250-59a8-49cb-b150-71cf26861298@e27g2000yqd.googlegroups.com>
References: <7e38b4ea-b699-4ed7-a872-f3b62163dcb8@p8g2000yqb.googlegroups.com>
NNTP-Posting-Host: 12.163.127.241
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1262904427 22740 127.0.0.1 (7 Jan 2010 22:47:07 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 7 Jan 2010 22:47:07 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: e27g2000yqd.googlegroups.com; posting-host=12.163.127.241; posting-account=QgXFPQoAAAB84vWxq6gT-rvod2LNGaSK
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/3.0.195.38 Safari/532.0,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!newsfeed.news2me.com!nx02.iad01.newshosting.com!newshosting.com!69.16.185.21.MISMATCH!npeer03.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!postnews.google.com!e27g2000yqd.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90554
Article PK: 410158

What I have this now doing is modifying the SQL correctly on the
datastore, but no matter what I try the retrieve() is always handing
me back a -1.

What do I have to do to get a SQL modified datastore to retrieve data?

(to get around the modifcation error I used the modify command
instead)

Thanks

On Jan 7, 3:21 pm, gooddawggy <gooddaw...@gmail.com> wrote:
> I am trying to design a large report that the user can select fields
> and build out the report depending on what they need.
>
> The problem that I am having is that I modify the default datastore
> with the new SQL.  When I capture the SQL it runs fine pulling
> directly from the database.  So the sql builds out fine.
>
> I capture the return on the SetSQLSelect() and it says that its
> modifying the SQL on the datastore just fine but its not. (no -1
> return)
>
> I try to then retrieve the datastore and I get a -1 on retrieve.  Not
> really an intuitive error code.  The transaction object doesn't help
> very much either.  No error there.
>
> I have tried re-connecting the datastore to the trans object after the
> SQL modification as well but that didn't work either.
>
> Also tried destroying the datastore and then re-doing it and modifying
> it but that didn't work.
>
> This is what the SQL is being modified from:
>
> SELECT active_dental_status=cli_clients_only.active_dental_status,
> consultant=cli_clients_only.consultant,
> code_id=cli_clients_only.code_id, Mnumber=cli_clients_only.old_acct,
> Client_name=cli_clients_only.sort_by
> FROM cli_clients_only
> LEFT JOIN cli_iar_summary ON cli_clients_only.code_id =
> cli_iar_summary.client_code_id_link
> LEFT JOIN cli_contacts_cli contact ON cli_clients_only.code_id =
> contact.client_code_id
> LEFT JOIN cli_mkt_info ON cli_clients_only.code_id =
> cli_mkt_info.client_code_id_link
> LEFT JOIN cli_retainer_info ON cli_clients_only.code_id =
> cli_retainer_info.client_code_id_link
> LEFT JOIN cli_goals_meeting_master cgmm ON cli_clients_only.code_id =
> cgmm.code_id_link
> AND cgmm.contact_type = '360'
> WHERE cli_clients_only.consultant = 'Consultant'
>
> This is what it is being modified to (there is only one column
> addition in the SELECT portion)
> SELECT active_dental_status=cli_clients_only.active_dental_status,
> consultant=cli_clients_only.consultant,
> code_id=cli_clients_only.code_id, Mnumber=cli_clients_only.old_acct,
> Client_name=cli_clients_only.sort_by
> , cli_clients_only.pendingNLC FROM cli_clients_only
> LEFT JOIN cli_iar_summary ON cli_clients_only.code_id =
> cli_iar_summary.client_code_id_link
> LEFT JOIN cli_contacts_cli contact ON cli_clients_only.code_id =
> contact.client_code_id
> LEFT JOIN cli_mkt_info ON cli_clients_only.code_id =
> cli_mkt_info.client_code_id_link
> LEFT JOIN cli_retainer_info ON cli_clients_only.code_id =
> cli_retainer_info.client_code_id_link
> LEFT JOIN cli_goals_meeting_master cgmm ON cli_clients_only.code_id =
> cgmm.code_id_link
> AND cgmm.contact_type = '360'
> WHERE cli_clients_only.consultant = 'Consultant'
>
> Any help with this problem would be much appreciated.
>
> Thanks


"Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel Posted on 2010-01-08 00:04:27.0Z
From: "Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel@yahoo!.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <7e38b4ea-b699-4ed7-a872-f3b62163dcb8@p8g2000yqb.googlegroups.com>
Subject: Re: Probem with dynamic SQL on DW then a retrieve - 1 error (PB 8)
Lines: 83
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b46768b@forums-1-dub>
Date: 7 Jan 2010 16:04:27 -0800
X-Trace: forums-1-dub 1262909067 10.22.241.152 (7 Jan 2010 16:04:27 -0800)
X-Original-Trace: 7 Jan 2010 16:04:27 -0800, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90556
Article PK: 410161

You don't get feedback from the DBMS in the transaction object when the SQL
came from a DS You need to look at the arguments to the DBError event of the
DS.
My guess is that you need to modify the table portion of the syntax to add
the additional column to the buffers as well as adding it to the SQL. If
there's a mismatch between the result set and the DS buffers, of course it
fails.
You might want to have a look at SyntaxFromSQL as well.
--
Report Bugs to Sybase: http://case-express.sybase.com/cx/welcome.do
Product Enhancement Requests:
http://my.isug.com/cgi-bin/1/c/submit_enhancement

"gooddawggy" <gooddawggy@gmail.com> wrote in message
news:7e38b4ea-b699-4ed7-a872-f3b62163dcb8@p8g2000yqb.googlegroups.com...
>I am trying to design a large report that the user can select fields
> and build out the report depending on what they need.
>
> The problem that I am having is that I modify the default datastore
> with the new SQL. When I capture the SQL it runs fine pulling
> directly from the database. So the sql builds out fine.
>
> I capture the return on the SetSQLSelect() and it says that its
> modifying the SQL on the datastore just fine but its not. (no -1
> return)
>
> I try to then retrieve the datastore and I get a -1 on retrieve. Not
> really an intuitive error code. The transaction object doesn't help
> very much either. No error there.
>
> I have tried re-connecting the datastore to the trans object after the
> SQL modification as well but that didn't work either.
>
> Also tried destroying the datastore and then re-doing it and modifying
> it but that didn't work.
>
> This is what the SQL is being modified from:
>
> SELECT active_dental_status=cli_clients_only.active_dental_status,
> consultant=cli_clients_only.consultant,
> code_id=cli_clients_only.code_id, Mnumber=cli_clients_only.old_acct,
> Client_name=cli_clients_only.sort_by
> FROM cli_clients_only
> LEFT JOIN cli_iar_summary ON cli_clients_only.code_id =
> cli_iar_summary.client_code_id_link
> LEFT JOIN cli_contacts_cli contact ON cli_clients_only.code_id =
> contact.client_code_id
> LEFT JOIN cli_mkt_info ON cli_clients_only.code_id =
> cli_mkt_info.client_code_id_link
> LEFT JOIN cli_retainer_info ON cli_clients_only.code_id =
> cli_retainer_info.client_code_id_link
> LEFT JOIN cli_goals_meeting_master cgmm ON cli_clients_only.code_id =
> cgmm.code_id_link
> AND cgmm.contact_type = '360'
> WHERE cli_clients_only.consultant = 'Consultant'
>
> This is what it is being modified to (there is only one column
> addition in the SELECT portion)
> SELECT active_dental_status=cli_clients_only.active_dental_status,
> consultant=cli_clients_only.consultant,
> code_id=cli_clients_only.code_id, Mnumber=cli_clients_only.old_acct,
> Client_name=cli_clients_only.sort_by
> , cli_clients_only.pendingNLC FROM cli_clients_only
> LEFT JOIN cli_iar_summary ON cli_clients_only.code_id =
> cli_iar_summary.client_code_id_link
> LEFT JOIN cli_contacts_cli contact ON cli_clients_only.code_id =
> contact.client_code_id
> LEFT JOIN cli_mkt_info ON cli_clients_only.code_id =
> cli_mkt_info.client_code_id_link
> LEFT JOIN cli_retainer_info ON cli_clients_only.code_id =
> cli_retainer_info.client_code_id_link
> LEFT JOIN cli_goals_meeting_master cgmm ON cli_clients_only.code_id =
> cgmm.code_id_link
> AND cgmm.contact_type = '360'
> WHERE cli_clients_only.consultant = 'Consultant'
>
> Any help with this problem would be much appreciated.
>
> Thanks
>


gooddawggy Posted on 2010-01-12 22:02:25.0Z
From: gooddawggy <gooddawggy@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Probem with dynamic SQL on DW then a retrieve - 1 error (PB 8)
Date: Tue, 12 Jan 2010 14:02:25 -0800 (PST)
Organization: http://groups.google.com
Lines: 135
Message-ID: <ad6659b3-7e7c-4ce3-a35f-06bb0cffb42b@u7g2000yqm.googlegroups.com>
References: <7e38b4ea-b699-4ed7-a872-f3b62163dcb8@p8g2000yqb.googlegroups.com> <4b46768b@forums-1-dub>
NNTP-Posting-Host: 12.163.127.241
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1263333745 32704 127.0.0.1 (12 Jan 2010 22:02:25 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 12 Jan 2010 22:02:25 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: u7g2000yqm.googlegroups.com; posting-host=12.163.127.241; posting-account=QgXFPQoAAAB84vWxq6gT-rvod2LNGaSK
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/3.0.195.38 Safari/532.0,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!newsfeed.news2me.com!newsfeed2.telusplanet.net!newsfeed.telus.net!npeer03.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!postnews.google.com!u7g2000yqm.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90578
Article PK: 410183

I dig around in a few places and did a couple of 'solutions'. Not
seeming to work. First tried
doing ls_dwsyntax = ids_adhoc.Describe("datawindow.syntax");

That gave me the complete syntax of the DW. I plugged in a column
heading as well as modified the SQL and that errored. Line 1 column
20 which I am getting to believe is a generic type of error message.

So I went instead with a datawindow object to see if that made
difference.

I did the dw_2.GetSQLSelect path and appended the new column to this
then did a Dw_2.create(new_sql). When I debug and capture the SQL it
runs fine.

But on the retrieve I get an improper release/version message on the
datawindow when I try to retrieve it (its all in version 8). I know
theoretically why it doesn't like the version (misplaced version
number not being created by the create function?), but I not quite
getting why the Create function isn't 'creating'.

Last I try doing dw_2.SetSQLSelect(new_sql) and that retrieved, but
not the new column. It doesn't show up at all.

I capture the SQL of the Datawindow before the retrieve and the SQL is
correct, the new column isn't coming across though. This is probably
the nearest that I have come to getting this thing working. Any ideas
as to why the SQL behind the DW is showing correctly, but the draw
itself still isn't showing a column for the new information? There is
no error on the datawindow when I retrieve it.

Feel like I am circling a solution but am missing something to tie the
problem up correctly.

Any insight that you could possibly give would be truly useful and
good.

Thanks




On Jan 7, 5:04 pm, "Jerry Siegel [TeamSybase]"

<jNOsSPAMsiegel@yahoo!.com> wrote:
> You don't get feedback from the DBMS in the transaction object when the SQL
> came from a DS You need to look at the arguments to the DBError event of the
> DS.
> My guess is that you need to modify the table portion of the syntax to add
> the additional column to the buffers as well as adding it to the SQL. If
> there's a mismatch between the result set and the DS buffers, of course it
> fails.
> You might want to have a look at SyntaxFromSQL as well.
> --
> Repor
t Bugs to Sybase:  http://case-express.sybase.com/cx/welcome.do
> Product Enhancement Requests:http://my.isug.com/cgi-bin/1/c/submit_enhancement
>
> "gooddawggy" <gooddaw...@gmail.com> wrote in message
>
> news:7e38b4ea-b699-4ed7-a872-f3b62163dcb8@p8g2000yqb.googlegroups.com...
>
>
>
> >I am trying to design a large report that the user can select fields
> > and build out the report depending on what they need.
>
> > The problem that I am having is that I modify the default datastore
> > with the new SQL.  When I capture the SQL it runs fine pulling
> > directly from the database.  So the sql builds out fine.
>
> > I capture the return on the SetSQLSelect() and it says that its
> > modifying the SQL on the datastore just fine but its not. (no -1
> > return)
>
> > I try to then retrieve the datastore and I get a -1 on retrieve.  Not
> > really an intuitive error code.  The transaction object doesn't help
> > very much either.  No error there.
>
> > I have tried re-connecting the datastore to the trans object after the
> > SQL modification as well but that didn't work either.
>
> > Also tried destroying the datastore and then re-doing it and modifying
> > it but that didn't work.
>
> > This is what the SQL is being modified from:
>
> > SELECT active_dental_status=cli_clients_only.active_dental_status,
> > consultant=cli_clients_only.consultant,
> > code_id=cli_clients_only.code_id, Mnumber=cli_clients_only.old_acct,
> > Client_name=cli_clients_only.sort_by
> > FROM cli_clients_only
> > LEFT JOIN cli_iar_summary ON cli_clients_only.code_id =
> > cli_iar_summary.client_code_id_link
> > LEFT JOIN cli_contacts_cli contact ON cli_clients_only.code_id =
> > contact.client_code_id
> > LEFT JOIN cli_mkt_info ON cli_clients_only.code_id =
> > cli_mkt_info.client_code_id_link
> > LEFT JOIN cli_retainer_info ON cli_clients_only.code_id =
> > cli_retainer_info.client_code_id_link
> > LEFT JOIN cli_goals_meeting_master cgmm ON cli_clients_only.code_id =
> > cgmm.code_id_link
> > AND cgmm.contact_type = '360'
> > WHERE cli_clients_only.consultant = 'Consultant'
>
> > This is what it is being modified to (there is only one column
> > addition in the SELECT portion)
> > SELECT active_dental_status=cli_clients_only.active_dental_status,
> > consultant=cli_clients_only.consultant,
> > code_id=cli_clients_only.code_id, Mnumber=cli_clients_only.old_acct,
> > Client_name=cli_clients_only.sort_by
> > , cli_clients_only.pendingNLC FROM cli_clients_only
> > LEFT JOIN cli_iar_summary ON cli_clients_only.code_id =
> > cli_iar_summary.client_code_id_link
> > LEFT JOIN cli_contacts_cli contact ON cli_clients_only.code_id =
> > contact.client_code_id
> > LEFT JOIN cli_mkt_info ON cli_clients_only.code_id =
> > cli_mkt_info.client_code_id_link
> > LEFT JOIN cli_retainer_info ON cli_clients_only.code_id =
> > cli_retainer_info.client_code_id_link
> > LEFT JOIN cli_goals_meeting_master cgmm ON cli_clients_only.code_id =
> > cgmm.code_id_link
> > AND cgmm.contact_type = '360'
> > WHERE cli_clients_only.consultant = 'Consultant'
>
> > Any help with this problem would be much appreciated.
>
> > Thanks


"Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel Posted on 2010-01-13 03:13:49.0Z
From: "Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel@yahoo!.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <7e38b4ea-b699-4ed7-a872-f3b62163dcb8@p8g2000yqb.googlegroups.com> <4b46768b@forums-1-dub> <ad6659b3-7e7c-4ce3-a35f-06bb0cffb42b@u7g2000yqm.googlegroups.com>
Subject: Re: Probem with dynamic SQL on DW then a retrieve - 1 error (PB 8)
Lines: 181
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b4d3a6d@forums-1-dub>
Date: 12 Jan 2010 19:13:49 -0800
X-Trace: forums-1-dub 1263352429 10.22.241.152 (12 Jan 2010 19:13:49 -0800)
X-Original-Trace: 12 Jan 2010 19:13:49 -0800, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90579
Article PK: 410184

Take a look at the syntax of a DW object and you should see that a column
has (usually) 3 references. In the table( portion there is a column=(
reference, which defines the column in the DW buffers. Example:
column=(type=long update=yes updatewhereclause=yes key=yes name=id
dbname="customer.id" )
Also in the table( portion there is a retrieve = ( section [this would be
omitted for an external source DW]
Separately, there is usually a visual column
column(band=detail id=1 alignment="1" tabsequence=10 border="0"
color="33554432" x="5" y="4" height="60" width="206" format="[general]"
html.valueishtml="0" name=id tag="Unique Identification number of the
customer" visible="1" edit.limit=0 edit.case=any edit.autoselect=yes
edit.autohscroll=yes font.face="MS Sans Serif" font.height="-8"
font.weight="400" font.family="2" font.pitch="2" font.charset="0"
background.mode="1" background.color="536870912" background.transparency="0"
background.gradient.color="8421504" background.gradient.transparency="0"
background.gradient.angle="0" background.brushmode="0"
background.gradient.repetition.mode="0"
background.gradient.repetition.count="0"
background.gradient.repetition.length="100" background.gradient.focus="0"
background.gradient.scale="100" background.gradient.spread="100"
tooltip.backcolor="134217752" tooltip.delay.initial="0"
tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0"
tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0"
tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )

which may be omitted if the column is there to be used in an expression or
in other processing but not seen.

The latter would be added with Modify('create ... Notice the id= which is
the ordinal of the column in the buffers. The id=1 means the first column(
in the buffer definition.

The select can be changed with SetSqlSelect or Modify, but that's usually
done to massage the where clause rather than the result set. The only way to
change the buffer definition is to replace the entire table( portion or redo
the whole syntax with Create.

Bottom line, if the select result set doesn't match the buffer definition
you've got problems.

--
Report Bugs to Sybase: http://case-express.sybase.com/cx/welcome.do
Product Enhancement Requests:
http://my.isug.com/cgi-bin/1/c/submit_enhancement


"gooddawggy" <gooddawggy@gmail.com> wrote in message
news:ad6659b3-7e7c-4ce3-a35f-06bb0cffb42b@u7g2000yqm.googlegroups.com...
I dig around in a few places and did a couple of 'solutions'. Not
seeming to work. First tried
doing ls_dwsyntax = ids_adhoc.Describe("datawindow.syntax");

That gave me the complete syntax of the DW. I plugged in a column
heading as well as modified the SQL and that errored. Line 1 column
20 which I am getting to believe is a generic type of error message.

So I went instead with a datawindow object to see if that made
difference.

I did the dw_2.GetSQLSelect path and appended the new column to this
then did a Dw_2.create(new_sql). When I debug and capture the SQL it
runs fine.

But on the retrieve I get an improper release/version message on the
datawindow when I try to retrieve it (its all in version 8). I know
theoretically why it doesn't like the version (misplaced version
number not being created by the create function?), but I not quite
getting why the Create function isn't 'creating'.

Last I try doing dw_2.SetSQLSelect(new_sql) and that retrieved, but
not the new column. It doesn't show up at all.

I capture the SQL of the Datawindow before the retrieve and the SQL is
correct, the new column isn't coming across though. This is probably
the nearest that I have come to getting this thing working. Any ideas
as to why the SQL behind the DW is showing correctly, but the draw
itself still isn't showing a column for the new information? There is
no error on the datawindow when I retrieve it.

Feel like I am circling a solution but am missing something to tie the
problem up correctly.

Any insight that you could possibly give would be truly useful and
good.

Thanks




On Jan 7, 5:04 pm, "Jerry Siegel [TeamSybase]"

<jNOsSPAMsiegel@yahoo!.com> wrote:
> You don't get feedback from the DBMS in the transaction object when the
> SQL
> came from a DS You need to look at the arguments to the DBError event of
> the
> DS.
> My guess is that you need to modify the table portion of the syntax to add
> the additional column to the buffers as well as adding it to the SQL. If
> there's a mismatch between the result set and the DS buffers, of course it
> fails.
> You might want to have a look at SyntaxFromSQL as well.
> --
> Repor
t Bugs to Sybase: http://case-express.sybase.com/cx/welcome.do
> Product Enhancement
> Requests:http://my.isug.com/cgi-bin/1/c/submit_enhancement
>
> "gooddawggy" <gooddaw...@gmail.com> wrote in message
>
> news:7e38b4ea-b699-4ed7-a872-f3b62163dcb8@p8g2000yqb.googlegroups.com...
>
>
>
> >I am trying to design a large report that the user can select fields
> > and build out the report depending on what they need.
>
> > The problem that I am having is that I modify the default datastore
> > with the new SQL. When I capture the SQL it runs fine pulling
> > directly from the database. So the sql builds out fine.
>
> > I capture the return on the SetSQLSelect() and it says that its
> > modifying the SQL on the datastore just fine but its not. (no -1
> > return)
>
> > I try to then retrieve the datastore and I get a -1 on retrieve. Not
> > really an intuitive error code. The transaction object doesn't help
> > very much either. No error there.
>
> > I have tried re-connecting the datastore to the trans object after the
> > SQL modification as well but that didn't work either.
>
> > Also tried destroying the datastore and then re-doing it and modifying
> > it but that didn't work.
>
> > This is what the SQL is being modified from:
>
> > SELECT active_dental_status=cli_clients_only.active_dental_status,
> > consultant=cli_clients_only.consultant,
> > code_id=cli_clients_only.code_id, Mnumber=cli_clients_only.old_acct,
> > Client_name=cli_clients_only.sort_by
> > FROM cli_clients_only
> > LEFT JOIN cli_iar_summary ON cli_clients_only.code_id =
> > cli_iar_summary.client_code_id_link
> > LEFT JOIN cli_contacts_cli contact ON cli_clients_only.code_id =
> > contact.client_code_id
> > LEFT JOIN cli_mkt_info ON cli_clients_only.code_id =
> > cli_mkt_info.client_code_id_link
> > LEFT JOIN cli_retainer_info ON cli_clients_only.code_id =
> > cli_retainer_info.client_code_id_link
> > LEFT JOIN cli_goals_meeting_master cgmm ON cli_clients_only.code_id =
> > cgmm.code_id_link
> > AND cgmm.contact_type = '360'
> > WHERE cli_clients_only.consultant = 'Consultant'
>
> > This is what it is being modified to (there is only one column
> > addition in the SELECT portion)
> > SELECT active_dental_status=cli_clients_only.active_dental_status,
> > consultant=cli_clients_only.consultant,
> > code_id=cli_clients_only.code_id, Mnumber=cli_clients_only.old_acct,
> > Client_name=cli_clients_only.sort_by
> > , cli_clients_only.pendingNLC FROM cli_clients_only
> > LEFT JOIN cli_iar_summary ON cli_clients_only.code_id =
> > cli_iar_summary.client_code_id_link
> > LEFT JOIN cli_contacts_cli contact ON cli_clients_only.code_id =
> > contact.client_code_id
> > LEFT JOIN cli_mkt_info ON cli_clients_only.code_id =
> > cli_mkt_info.client_code_id_link
> > LEFT JOIN cli_retainer_info ON cli_clients_only.code_id =
> > cli_retainer_info.client_code_id_link
> > LEFT JOIN cli_goals_meeting_master cgmm ON cli_clients_only.code_id =
> > cgmm.code_id_link
> > AND cgmm.contact_type = '360'
> > WHERE cli_clients_only.consultant = 'Consultant'
>
> > Any help with this problem would be much appreciated.
>
> > Thanks


gooddawggy Posted on 2010-01-13 17:04:06.0Z
From: gooddawggy <gooddawggy@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Probem with dynamic SQL on DW then a retrieve - 1 error (PB 8)
Date: Wed, 13 Jan 2010 09:04:06 -0800 (PST)
Organization: http://groups.google.com
Lines: 262
Message-ID: <ee302d54-82e0-4cbb-877b-ab903039e423@u41g2000yqe.googlegroups.com>
References: <7e38b4ea-b699-4ed7-a872-f3b62163dcb8@p8g2000yqb.googlegroups.com> <4b46768b@forums-1-dub> <ad6659b3-7e7c-4ce3-a35f-06bb0cffb42b@u7g2000yqm.googlegroups.com> <4b4d3a6d@forums-1-dub>
NNTP-Posting-Host: 12.163.127.241
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1263402247 1783 127.0.0.1 (13 Jan 2010 17:04:07 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 13 Jan 2010 17:04:07 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: u41g2000yqe.googlegroups.com; posting-host=12.163.127.241; posting-account=QgXFPQoAAAB84vWxq6gT-rvod2LNGaSK
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/3.0.195.38 Safari/532.0,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!newsfeed.news2me.com!newsfeed2.telusplanet.net!newsfeed.telus.net!news.glorb.com!postnews.google.com!u41g2000yqe.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90580
Article PK: 410185

Thank you a great deal for the input.

Since I have roughly 81 columns, and all their combinations, that's
going to be murder.

One last question if I may.

The whole point of me writing this is so that clients can generate a
report that is exported to CSV format or excel.

Is there a way that I can suppress columns when saving to a CSV?

What I did was draw the report with multiple columns. Some are
baseline and will always be in every report others are optionals.
When the customer de-selects an option I can change the width to zero
on the column heading and the column itself. (yes I work long into
the night at times :) )

It won't speed up the report, these run slow anyways, but for display
purposes its like I am adding and removing.

The only problem that I seem to have is putting it to Excel. I tried
saving it as an HTML then use OLE to create an excel file with it, but
that didn't seem to come out right.

So the question is: Is there a way to suppress these fields when
saving to CSV or Excel? The code below works for HTML, it doesn't work
for Excel (actually the excel is never generated with this code but
doesnt error)

Thanks

ls_docname = 'C:\Documents and Settings\'+ string(SQLCA.logid)
+'\Desktop\Adhoc Reports\Adhoc.html'

if dw_2.SaveAs(ls_docname, HTMLTable!, True) = -1 then
MessageBox("Warning", "Unable to export data. Error writing to
file!", Exclamation!)
return
end if

// Convert HTML file to Excel native format
OLEObject excel
excel = CREATE OLEObject
if excel.ConnectToObject(ls_docname) = 0 then
excel.application.DisplayAlerts = FALSE
excel.application.workbooks(1).Parent.Windows
(excel.application.workbooks(1).Name).Visible = True
excel.application.workbooks(1).saveas('C:\Documents and Settings
\'+ string(SQLCA.logid) +'\Desktop\Adhoc Reports\Adhoc.xls', 39)
excel.application.workbooks(1).close()
end if

DESTROY excel

On Jan 12, 8:13 pm, "Jerry Siegel [TeamSybase]"

<jNOsSPAMsiegel@yahoo!.com> wrote:
> Take a look at the syntax of a DW object and you should see that a column
> has (usually) 3 references. In the table( portion there is a column=(
> reference, which defines the column in the DW buffers.  Example:
> column=(type=long update=yes updatewhereclause=yes key=yes name=id
> dbname="customer.id" )
> Also in the table( portion there is a retrieve = (  section [this would be
> omitted for an external source DW]
> Separately, there is usually a visual column
> column(band=detail id=1 alignment="1" tabsequence=10 border="0"
> color="33554432" x="5" y="4" height="60" width="206" format="[general]"
> html.valueishtml="0"  name=id  tag="Unique Identification number of the
> customer" visible="1" edit.limit=0 edit.case=any edit.autoselect=yes
> edit.autohscroll=yes  font.face="MS Sans Serif" font.height="-8"
> font.weight="400"  font.family="2" font.pitch="2" font.charset="0"
> background.mode="1" background.color="536870912" background.transparency="0"
> background.gradient.color="8421504" background.gradient.transparency="0"
> background.gradient.angle="0" background.brushmode="0"
> background.gradient.repetition.mode="0"
> background.gradient.repetition.count="0"
> background.gradient.repetition.length="100" background.gradient.focus="0"
> background.gradient.scale="100" background.gradient.spread="100"
> tooltip.backcolor="134217752" tooltip.delay.initial="0"
> tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0"
> tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0"
> tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )
>
> which may be omitted if the column is there to be used in an expression or
> in other processing but not seen.
>
> The latter would be added with Modify('create ...  Notice the id= which is
> the ordinal of the column in the buffers. The id=1 means the first column(
> in the buffer definition.
>
> The select can be changed with SetSqlSelect or Modify, but that's usually
> done to massage the where clause rather than the result set. The only way to
> change the buffer definition is to replace the entire table( portion or redo
> the whole syntax with Create.
>
> Bottom line, if the select result set doesn't match the buffer definition
> you've got problems.
>
> --
> Report Bugs to Sybase:  http://case-express.sybase.com/cx/welcome.do
> Product Enhancement Requests:http://my.isug.com/cgi-bin/1/c/submit_enhancement
>
> "gooddawggy" <gooddaw...@gmail.com> wrote in message
>
> news:ad6659b3-7e7c-4ce3-a35f-06bb0cffb42b@u7g2000yqm.googlegroups.com...
> I dig around in a few places and did a couple of 'solutions'. Not
> seeming to work.  First tried
> doing ls_dwsyntax = ids_adhoc.Describe("datawindow.syntax");
>
> That gave me the complete syntax of the DW.  I plugged in a column
> heading as well as modified the SQL and that errored.  Line 1 column
> 20 which I am getting to believe is a generic type of error message.
>
> So I went instead with a datawindow object to see if that made
> difference.
>
> I did the dw_2.GetSQLSelect path and appended the new column to this
> then did a Dw_2.create(new_sql).  When I debug and capture the SQL it
> runs fine.
>
> But on the retrieve I get an improper release/version message on the
> datawindow when I try to retrieve it (its all in version 8).  I know
> theoretically why it doesn't like the version (misplaced version
> number not being created by the create function?), but I not quite
> getting why the Create function isn't 'creating'.
>
> Last I  try doing dw_2.SetSQLSelect(new_sql) and that retrieved, but
> not the new column.  It doesn't show up at all.
>
> I capture the SQL of the Datawindow before the retrieve and the SQL is
> correct, the new column isn't coming across though.  This is probably
> the nearest that I have come to getting this thing working.  Any ideas
> as to why the SQL behind the DW is showing correctly, but the draw
> itself still isn't showing a column for the new information?  There is
> no error on the datawindow when I retrieve it.
>
> Feel like I am circling a solution but am missing something to tie the
> problem up correctly.
>
> Any insight that you could possibly give would be truly useful and
> good.
>
> Thanks
>
> On Jan 7, 5:04 pm, "Jerry Siegel [TeamSybase]"<jNOsSPAMsiegel@yahoo!.com> wrote:
> > You don't get feedback from the DBMS in the transaction object when the
> > SQL
> > came from a DS You need to look at the arguments to the DBError event of
> > the
> > DS.
> > My guess is that you need to modify the table portion of the syntax to add
> > the additional column to the buffers as well as adding it to the SQL. If
> > there's a mismatch between the result set and the DS buffers, of course it
> > fails.
> > You might want to have a look at SyntaxFromSQL as well.
> > --
> > Repor
>
> t Bugs to Sybase:http://case-express.sybase.com/cx/welcome.do
>
>
>
> > Product Enhancement
> > Requests:http://my.isug.com/cgi-bin/1/c/submit_enhancement
>
> > "gooddawggy" <gooddaw...@gmail.com> wrote in message
>
> >news:7e38b4ea-b699-4ed7-a872-f3b62163dcb8@p8g2000yqb.googlegroups.com...
>
> > >I am trying to design a large report that the user can select fields
> > > and build out the report depending on what they need.
>
> > > The problem that I am having is that I modify the default datastore
> > > with the new SQL. When I capture the SQL it runs fine pulling
> > > directly from the database. So the sql builds out fine.
>
> > > I capture the return on the SetSQLSelect() and it says that its
> > > modifying the SQL on the datastore just fine but its not. (no -1
> > > return)
>
> > > I try to then retrieve the datastore and I get a -1 on retrieve. Not
> > > really an intuitive error code. The transaction object doesn't help
> > > very much either. No error there.
>
> > > I have tried re-connecting the datastore to the trans object after the
> > > SQL modification as well but that didn't work either.
>
> > > Also tried destroying the datastore and then re-doing it and modifying
> > > it but that didn't work.
>
> > > This is what the SQL is being modified from:
>
> > > SELECT active_dental_status=cli_clients_only.active_dental_status,
> > > consultant=cli_clients_only.consultant,
> > > code_id=cli_clients_only.code_id, Mnumber=cli_clients_only.old_acct,
> > > Client_name=cli_clients_only.sort_by
> > > FROM cli_clients_only
> > > LEFT JOIN cli_iar_summary ON cli_clients_only.code_id =
> > > cli_iar_summary.client_code_id_link
> > > LEFT JOIN cli_contacts_cli contact ON cli_clients_only.code_id =
> > > contact.client_code_id
> > > LEFT JOIN cli_mkt_info ON cli_clients_only.code_id =
> > > cli_mkt_info.client_code_id_link
> > > LEFT JOIN cli_retainer_info ON cli_clients_only.code_id =
> > > cli_retainer_info.client_code_id_link
> > > LEFT JOIN cli_goals_meeting_master cgmm ON cli_clients_only.code_id =
> > > cgmm.code_id_link
> > > AND cgmm.contact_type = '360'
> > > WHERE cli_clients_only.consultant = 'Consultant'
>
> > > This is what it is being modified to (there is only one column
> > > addition in the SELECT portion)
> > > SELECT active_dental_status=cli_clients_only.active_dental_status,
> > > consultant=cli_clients_only.consultant,
> > > code_id=cli_clients_only.code_id, Mnumber=cli_clients_only.old_acct,
> > > Client_name=cli_clients_only.sort_by
> > > , cli_clients_only.pendingNLC FROM cli_clients_only
> > > LEFT JOIN cli_iar_summary ON cli_clients_only.code_id =
> > > cli_iar_summary.client_code_id_link
> > > LEFT JOIN cli_contacts_cli contact ON cli_clients_only.code_id =
> > > contact.client_code_id
> > > LEFT JOIN cli_mkt_info ON cli_clients_only.code_id =
> > > cli_mkt_info.client_code_id_link
> > > LEFT JOIN cli_retainer_info ON cli_clients_only.code_id =
> > > cli_retainer_info.client_code_id_link
> > > LEFT JOIN cli_goals_meeting_master cgmm ON cli_clients_only.code_id =
> > > cgmm.code_id_link
> > > AND cgmm.contact_type = '360'
> > > WHERE cli_clients_only.consultant = 'Consultant'
>
> > > Any help with this problem would be much appreciated.
>
> > > Thanks


gooddawggy Posted on 2010-01-18 23:06:05.0Z
From: gooddawggy <gooddawggy@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Probem with dynamic SQL on DW then a retrieve - 1 error (PB 8)
Date: Mon, 18 Jan 2010 15:06:05 -0800 (PST)
Organization: http://groups.google.com
Lines: 291
Message-ID: <b5a143b0-1996-47e4-916f-d61d6d4b1c85@r5g2000yqb.googlegroups.com>
References: <7e38b4ea-b699-4ed7-a872-f3b62163dcb8@p8g2000yqb.googlegroups.com> <4b46768b@forums-1-dub> <ad6659b3-7e7c-4ce3-a35f-06bb0cffb42b@u7g2000yqm.googlegroups.com> <4b4d3a6d@forums-1-dub> <ee302d54-82e0-4cbb-877b-ab903039e423@u41g2000yqe.googlegroups.com>
NNTP-Posting-Host: 12.163.127.241
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1263855966 691 127.0.0.1 (18 Jan 2010 23:06:06 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 18 Jan 2010 23:06:06 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: r5g2000yqb.googlegroups.com; posting-host=12.163.127.241; posting-account=QgXFPQoAAAB84vWxq6gT-rvod2LNGaSK
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Avant Browser; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; OfficeLiveConnector.1.3; OfficeLivePatch.0.0),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp1.roc.gblx.net!nntp.gblx.net!nntp.gblx.net!newsfeed.yul.equant.net!news-raspail.gip.net!news.gsl.net!gip.net!aotearoa.belnet.be!news.belnet.be!goblin1!goblin.stu.neva.ru!postnews.google.com!r5g2000yqb.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90596
Article PK: 410201

Thanks for the help on this. I wound up retrieving the entire report
in the back ground when the user hits retrieve. Then I use the excel
object and delete the columns selected on the screen to that
datawindow. Its a bit clunky, but it works.



Thanks for your help on this. Much appreciated.

On Jan 13, 10:04 am, gooddawggy <gooddaw...@gmail.com> wrote:
> Thank you a great deal for the input.
>
> Since I have roughly 81 columns, and all their combinations, that's
> going to be murder.
>
> One last question if I may.
>
> The whole point of me writing this is so that clients can generate a
> report that is exported to CSV format or excel.
>
> Is there a way that I can suppress columns when saving to a CSV?
>
> What I did was draw the report with multiple columns.  Some are
> baseline and will always be in every report others are optionals.
> When the customer de-selects an option I can change the width to zero
> on the column heading and the column itself.  (yes I work long into
> the night at times :) )
>
> It won't speed up the report, these run slow anyways, but for display
> purposes its like I  am adding and removing.
>
> The only problem that I seem to have is putting it to Excel.  I tried
> saving it as an HTML then use OLE to create an excel file with it, but
> that didn't seem to come out right.
>
> So the question is: Is there a way to suppress these fields when
> saving to CSV or Excel? The code below works for HTML, it doesn't work
> for Excel (actually the excel is never generated with this code but
> doesnt error)
>
> Thanks
>
> ls_docname = 'C:\Documents and Settings\'+ string(SQLCA.logid)
> +'\Desktop\Adhoc Reports\Adhoc.html'
>
> if dw_2.SaveAs(ls_docname, HTMLTable!, True) = -1 then
>     MessageBox("Warning", "Unable to export data. Error writing to
> file!", Exclamation!)
>     return
> end if
>
> // Convert HTML file to Excel native format
> OLEObject excel
> excel = CREATE OLEObject
> if excel.ConnectToObject(ls_docname) = 0 then
>     excel.application.DisplayAlerts = FALSE
>     excel.application.workbooks(1).Parent.Windows
> (excel.application.workbooks(1).Name).Visible = True
>     excel.application.workbooks(1).saveas('C:\Documents and Settings
> \'+ string(SQLCA.logid) +'\Desktop\Adhoc Reports\Adhoc.xls', 39)
>     excel.application.workbooks(1).close()
> end if
>
> DESTROY excel
>
> On Jan 12, 8:13 pm, "Jerry Siegel [TeamSybase]"
>
>
>
> <jNOsSPAMsiegel@yahoo!.com> wrote:
> > Take a look at the syntax of a DW object and you should see that a column
> > has (usually) 3 references. In the table( portion there is a column=(
> > reference, which defines the column in the DW buffers.  Example:
> > column=(type=long update=yes updatewhereclause=yes key=yes name=id
> > dbname="customer.id" )
> > Also in the table( portion there is a retrieve = (  section [this would be
> > omitted for an external source DW]
> > Separately, there is usually a visual column
> > column(band=detail id=1 alignment="1" tabsequence=10 border="0"
> > color="33554432" x="5" y="4" height="60" width="206" format="[general]"
> > html.valueishtml="0"  name=id  tag="Unique Identification number of the
> > customer" visible="1" edit.limit=0 edit.case=any edit.autoselect=yes
> > edit.autohscroll=yes  font.face="MS Sans Serif" font.height="-8"
> > font.weight="400"  font.family="2" font.pitch="2" font.charset="0"
> > background.mode="1" background.color="536870912" background.transparency="0"
> > background.gradient.color="8421504" background.gradient.transparency="0"
> > background.gradient.angle="0" background.brushmode="0"
> > background.gradient.repetition.mode="0"
> > background.gradient.repetition.count="0"
> > background.gradient.repetition.length="100" background.gradient.focus="0"
> > background.gradient.scale="100" background.gradient.spread="100"
> > tooltip.backcolor="134217752" tooltip.delay.initial="0"
> > tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0"
> > tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0"
> > tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )
>
> > which may be omitted if the column is there to be used in an expression or
> > in other processing but not seen.
>
> > The latter would be added with Modify('create ...  Notice the id= which is
> > the ordinal of the column in the buffers. The id=1 means the first column(
> > in the buffer definition.
>
> > The select can be changed with SetSqlSelect or Modify, but that's usually
> > done to massage the where clause rather than the result set. The only way to
> > change the buffer definition is to replace the entire table( portion or redo
> > the whole syntax with Create.
>
> > Bottom line, if the select result set doesn't match the buffer definition
> > you've got problems.
>
> > --
> > Report Bugs to Sybase:  http://case-express.sybase.com/cx/welcome.do
> > Product Enhancement Requests:http://my.isug.com/cgi-bin/1/c/submit_enhancement
>
> > "gooddawggy" <gooddaw...@gmail.com> wrote in message
>
> >news:ad6659b3-7e7c-4ce3-a35f-06bb0cffb42b@u7g2000yqm.googlegroups.com...
> > I dig around in a few places and did a couple of 'solutions'. Not
> > seeming to work.  First tried
> > doing ls_dwsyntax = ids_adhoc.Describe("datawindow.syntax");
>
> > That gave me the complete syntax of the DW.  I plugged in a column
> > heading as well as modified theSQLand that errored.  Line 1 column
> > 20 which I am getting to believe is a generic type of error message.
>
> > So I went instead with adatawindowobject to see if that made
> > difference.
>
> > I did the dw_2.GetSQLSelect path and appended the new column to this
> > then did a Dw_2.create(new_sql).  When I debug and capture theSQLit
> > runs fine.
>
> > But on the retrieve I get an improper release/version message on the
> >datawindowwhen I try to retrieve it (its all in version 8).  I know
> > theoretically why it doesn't like the version (misplaced version
> > number not being created by the create function?), but I not quite
> > getting why the Create function isn't 'creating'.
>
> > Last I  try doing dw_2.SetSQLSelect(new_sql) and that retrieved, but
> > not the new column.  It doesn't show up at all.
>
> > I capture theSQLof theDatawindowbefore the retrieve and theSQLis
> > correct, the new column isn't coming across though.  This is probably
> > the nearest that I have come to getting this thing working.  Any ideas
> > as to why theSQLbehind the DW is showing correctly, but the draw
> > itself still isn't showing a column for the new information?  There is
> > no error on thedatawindowwhen I retrieve it.
>
> > Feel like I am circling a solution but am missing something to tie the
> > problem up correctly.
>
> > Any insight that you could possibly give would be truly useful and
> > good.
>
> > Thanks
>
> > On Jan 7, 5:04 pm, "Jerry Siegel [TeamSybase]"<jNOsSPAMsiegel@yahoo!.com> wrote:
> > > You don't get feedback from the DBMS in the transaction object when the
> > >SQL
> > > came from a DS You need to look at the arguments to the DBError event of
> > > the
> > > DS.
> > > My guess is that you need to modify the table portion of the syntax to add
> > > the additional column to the buffers as well as adding it to theSQL. If
> > > there's a mismatch between the result set and the DS buffers, of course it
> > > fails.
> > > You might want to have a look at SyntaxFromSQL as well.
> > > --
> > > Repor
>
> > t Bugs to Sybase:http://case-express.sybase.com/cx/welcome.do
>
> > > Product Enhancement
> > > Requests:http://my.isug.com/cgi-bin/1/c/submit_enhancement
>
> > > "gooddawggy" <gooddaw...@gmail.com> wrote in message
>
> > >news:7e38b4ea-b699-4ed7-a872-f3b62163dcb8@p8g2000yqb.googlegroups.com...
>
> > > >I am trying to design a large report that the user can select fields
> > > > and build out the report depending on what they need.
>
> > > > The problem that I am having is that I modify the default datastore
> > > > with the newSQL. When I capture theSQLit runs fine pulling
> > > > directly from the database. So thesqlbuilds out fine.
>
> > > > I capture the return on the SetSQLSelect() and it says that its
> > > > modifying theSQLon the datastore just fine but its not. (no -1
> > > > return)
>
> > > > I try to then retrieve the datastore and I get a -1 on retrieve. Not
> > > > really an intuitive error code. The transaction object doesn't help
> > > > very much either. No error there.
>
> > > > I have tried re-connecting the datastore to the trans object after the
> > > >SQLmodification as well but that didn't work either.
>
> > > > Also tried destroying the datastore and then re-doing it and modifying
> > > > it but that didn't work.
>
> > > > This is what theSQLis being modified from:
>
> > > > SELECT active_dental_status=cli_clients_only.active_dental_status,
> > > > consultant=cli_clients_only.consultant,
> > > > code_id=cli_clients_only.code_id, Mnumber=cli_clients_only.old_acct,
> > > > Client_name=cli_clients_only.sort_by
> > > > FROM cli_clients_only
> > > > LEFT JOIN cli_iar_summary ON cli_clients_only.code_id =
> > > > cli_iar_summary.client_code_id_link
> > > > LEFT JOIN cli_contacts_cli contact ON cli_clients_only.code_id =
> > > > contact.client_code_id
> > > > LEFT JOIN cli_mkt_info ON cli_clients_only.code_id =
> > > > cli_mkt_info.client_code_id_link
> > > > LEFT JOIN cli_retainer_info ON cli_clients_only.code_id =
> > > > cli_retainer_info.client_code_id_link
> > > > LEFT JOIN cli_goals_meeting_master cgmm ON cli_clients_only.code_id =
> > > > cgmm.code_id_link
> > > > AND cgmm.contact_type = '360'
> > > > WHERE cli_clients_only.consultant = 'Consultant'
>
> > > > This is what it is being modified to (there is only one column
> > > > addition in the SELECT portion)
> > > > SELECT active_dental_status=cli_clients_only.active_dental_status,
> > > > consultant=cli_clients_only.consultant,
> > > > code_id=cli_clients_only.code_id, Mnumber=cli_clients_only.old_acct,
> > > > Client_name=cli_clients_only.sort_by
> > > > , cli_clients_only.pendingNLC FROM cli_clients_only
> > > > LEFT JOIN cli_iar_summary ON cli_clients_only.code_id =
> > > > cli_iar_summary.client_code_id_link
> > > > LEFT JOIN cli_contacts_cli contact ON cli_clients_only.code_id =
> > > > contact.client_code_id
> > > > LEFT JOIN cli_mkt_info ON cli_clients_only.code_id =
> > > > cli_mkt_info.client_code_id_link
> > > > LEFT JOIN cli_retainer_info ON cli_clients_only.code_id =
> > > > cli_retainer_info.client_code_id_link
> > > > LEFT JOIN cli_goals_meeting_master cgmm ON cli_clients_only.code_id =
> > > > cgmm.code_id_link
> > > > AND cgmm.contact_type = '360'
> > > > WHERE cli_clients_only.consultant = 'Consultant'
>
> > > > Any help
>
> ...
>
> read more »- Hide quoted text -
>
> - Show quoted text -