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.

Union works ok in WISQL but errors in InfoMaker 5.02/Powerbuilder5.02/ db admin.

2 posts in PB Infomaker Report Writer Last posting was on 1997-12-03 02:04:06.0Z
clarcher Posted on 1997-12-02 18:40:57.0Z
Newsgroups: sybase.public.infomaker.general
From: "clarcher" <clarcher@gte.net>
Subject: Union works ok in WISQL but errors in InfoMaker 5.02/Powerbuilder5.02/ db admin.
X-Newsreader: Microsoft Internet News 4.70.1161
Date: Tue, 02 Dec 1997 13:40:57 -0500
Lines: 91
Message-ID: <348_01bcff5a$d98d0ad0$269473cf@gwy2b10>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:7377
Article PK: 244432

I have a multiple Union All SQL statement which works ok in WISQL (Sybase
11 backend) but yields a syntax error near ...Unions in 5.02. This same
syntax worked ok in InfoMaker 4.00 but not in Infomaker 5.02 or
Powerbuilder 5.02. (in database painter ... database admin).

The end-user requirements are best met with multiple passes of the data
with a UNION ALL combining each pass. The reporting must be in Infomaker
5.0x and is saved-as csv file (each sys_id and status should be on
separate lines). Any idea on making this work. (the work-around of using
a stored procedure to implement Unions is unacceptable.)

Any suggestions?

TIA

Chet Archer

The actual syntax of the SQL is:
start--->
SELECT dbo.SALES_EXTRACT.sys_id,
'A' status
FROM dbo.SALES_EXTRACT
where ( dbo.SALES_EXTRACT.service_end is null ) AND
( dbo.SALES_EXTRACT.service_start >= convert(datetime,'11/1/97') )
AND
( dbo.SALES_EXTRACT.service_start <= convert(datetime,'11/30/97')
) and
( dbo.SALES_EXTRACT.compensable_key in ('006','007','008') ) and
( dbo.SALES_EXTRACT.agency_external ='ISB' )
UNION ALL
SELECT dbo.SALES_EXTRACT.sys_id,
'P' status
FROM dbo.SALES_EXTRACT
WHERE ( dbo.SALES_EXTRACT.service_end is null ) AND
( dateadd(dd,19, dbo.SALES_EXTRACT.service_start) >
convert(datetime,'11/30/97') ) and
( dbo.SALES_EXTRACT.compensable_key in ('006','007','008') )
and
( dbo.SALES_EXTRACT.component_id in
(11070,11071,11072,11073,11089,11091,11105,11201)) and
( dbo.SALES_EXTRACT.agency_external ='ISB' )
UNION ALL
SELECT dbo.SALES_EXTRACT.sys_id,
'A' status
FROM dbo.SALES_EXTRACT
WHERE ( dbo.SALES_EXTRACT.service_end is null ) AND
( dateadd(dd, 19, dbo.SALES_EXTRACT.service_start)
<= convert(datetime,'11/30/97') ) and
( dbo.SALES_EXTRACT.compensable_key in ('006','007','008') )
and
( dbo.SALES_EXTRACT.component_id in
(11070,11071,11072,11073,11089,11091,11105,11201)) and
( dbo.SALES_EXTRACT.agency_external ='ISB' )
UNION ALL
SELECT dbo.SALES_EXTRACT.sys_id,
'P' status
FROM dbo.SALES_EXTRACT
where ( dbo.SALES_EXTRACT.service_end is null ) AND
( dbo.SALES_EXTRACT.service_start >= convert(datetime,'11/1/97') )
AND
( dbo.SALES_EXTRACT.service_start <= convert(datetime,'11/30/97')
) and
( dbo.SALES_EXTRACT.compensable_key in ('006','007','008') )
and
( dbo.SALES_EXTRACT.component_id in
(11031,11032,11074,11076,11081,11082, 11087,11088,11090,11094,11200,
11202,11207 )) and
( dbo.SALES_EXTRACT.agency_external ='ISB' )
UNION ALL
SELECT dbo.SALES_EXTRACT.sys_id,
'A' status
FROM dbo.SALES_EXTRACT
where ( dbo.SALES_EXTRACT.service_end is null ) AND
( dbo.SALES_EXTRACT.service_start >= convert(datetime,'11/1/97') )
AND
( dbo.SALES_EXTRACT.service_start <= convert(datetime,'11/30/97')
) and
( dbo.SALES_EXTRACT.compensable_key in ('006','007','008') )
and
( dbo.SALES_EXTRACT.component_id not in
(11070,11071,11072,11073,11089,11091,11105,11201)) and
( dbo.SALES_EXTRACT.component_id not in
(11031,11032,11074,11076,11081,11082, 11087,11088,11090,11094,11200,
11202,11207 )) and
( dbo.SALES_EXTRACT.agency_external ='ISB' )
order by 1 ;
<--- end


Stephen Dupre[Powersoft Tools, Sybase, Inc.] Posted on 1997-12-03 02:04:06.0Z
Newsgroups: sybase.public.infomaker.general
Date: Tue, 02 Dec 1997 21:04:06 -0500
From: "Stephen Dupre[Powersoft Tools, Sybase, Inc.]" <sdupre@powersoft.com>
Reply-To: sdupre@powersoft.com
Organization: Powersoft Tools
X-Mailer: Mozilla 3.0Gold (WinNT; U)
MIME-Version: 1.0
Subject: Re: Union works ok in WISQL but errors in InfoMaker 5.02/Powerbuilder5.02/ db admin.
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 96
Message-ID: <348_3484BE16.55C9@powersoft.com>
References: <348_01bcff5a$d98d0ad0$269473cf@gwy2b10>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:7375
Article PK: 244429


clarcher wrote:
>
> I have a multiple Union All SQL statement which works ok in WISQL (Sybase
> 11 backend) but yields a syntax error near ...Unions in 5.02. This same
> syntax worked ok in InfoMaker 4.00 but not in Infomaker 5.02 or
> Powerbuilder 5.02. (in database painter ... database admin).
>
> The end-user requirements are best met with multiple passes of the data
> with a UNION ALL combining each pass. The reporting must be in Infomaker
> 5.0x and is saved-as csv file (each sys_id and status should be on
> separate lines). Any idea on making this work. (the work-around of using
> a stored procedure to implement Unions is unacceptable.)
>
> Any suggestions?
>
> TIA
>
> Chet Archer
>
> The actual syntax of the SQL is:
> start--->
> SELECT dbo.SALES_EXTRACT.sys_id,
> 'A' status
> FROM dbo.SALES_EXTRACT
> where ( dbo.SALES_EXTRACT.service_end is null ) AND
> ( dbo.SALES_EXTRACT.service_start >= convert(datetime,'11/1/97') )
> AND
> ( dbo.SALES_EXTRACT.service_start <= convert(datetime,'11/30/97')
> ) and
> ( dbo.SALES_EXTRACT.compensable_key in ('006','007','008') ) and
> ( dbo.SALES_EXTRACT.agency_external ='ISB' )
> UNION ALL
> SELECT dbo.SALES_EXTRACT.sys_id,
> 'P' status
> FROM dbo.SALES_EXTRACT
> WHERE ( dbo.SALES_EXTRACT.service_end is null ) AND
> ( dateadd(dd,19, dbo.SALES_EXTRACT.service_start) >
> convert(datetime,'11/30/97') ) and
> ( dbo.SALES_EXTRACT.compensable_key in ('006','007','008') )
> and
> ( dbo.SALES_EXTRACT.component_id in
> (11070,11071,11072,11073,11089,11091,11105,11201)) and
> ( dbo.SALES_EXTRACT.agency_external ='ISB' )
> UNION ALL
> SELECT dbo.SALES_EXTRACT.sys_id,
> 'A' status
> FROM dbo.SALES_EXTRACT
> WHERE ( dbo.SALES_EXTRACT.service_end is null ) AND
> ( dateadd(dd, 19, dbo.SALES_EXTRACT.service_start)
> <= convert(datetime,'11/30/97') ) and
> ( dbo.SALES_EXTRACT.compensable_key in ('006','007','008') )
> and
> ( dbo.SALES_EXTRACT.component_id in
> (11070,11071,11072,11073,11089,11091,11105,11201)) and
> ( dbo.SALES_EXTRACT.agency_external ='ISB' )
> UNION ALL
> SELECT dbo.SALES_EXTRACT.sys_id,
> 'P' status
> FROM dbo.SALES_EXTRACT
> where ( dbo.SALES_EXTRACT.service_end is null ) AND
> ( dbo.SALES_EXTRACT.service_start >= convert(datetime,'11/1/97') )
> AND
> ( dbo.SALES_EXTRACT.service_start <= convert(datetime,'11/30/97')
> ) and
> ( dbo.SALES_EXTRACT.compensable_key in ('006','007','008') )
> and
> ( dbo.SALES_EXTRACT.component_id in
> (11031,11032,11074,11076,11081,11082, 11087,11088,11090,11094,11200,
> 11202,11207 )) and
> ( dbo.SALES_EXTRACT.agency_external ='ISB' )
> UNION ALL
> SELECT dbo.SALES_EXTRACT.sys_id,
> 'A' status
> FROM dbo.SALES_EXTRACT
> where ( dbo.SALES_EXTRACT.service_end is null ) AND
> ( dbo.SALES_EXTRACT.service_start >= convert(datetime,'11/1/97') )
> AND
> ( dbo.SALES_EXTRACT.service_start <= convert(datetime,'11/30/97')
> ) and
> ( dbo.SALES_EXTRACT.compensable_key in ('006','007','008') )
> and
> ( dbo.SALES_EXTRACT.component_id not in
> (11070,11071,11072,11073,11089,11091,11105,11201)) and
> ( dbo.SALES_EXTRACT.component_id not in
> (11031,11032,11074,11076,11081,11082, 11087,11088,11090,11094,11200,
> 11202,11207 )) and
> ( dbo.SALES_EXTRACT.agency_external ='ISB' )
> order by 1 ;
> <--- end
>
>

Try ModifySyntax=0 in the DBParm.

Stephen Dupre
Powersoft Tools