Problem with Union & Union Vs Array

[Adrian Clayfield <adclay@IBT.COM.AU>]

Im using:

Version: SQRW/3.0.13.3/PC/Windows-NT 3.51/Sybase 10.0.3/May 28 1996

 

Hello everyone,

I've spent most of the day trying to get a Union working in SQR.. It retrieves 4 different sets of posted total amts and encumbered amts from the Ledger table. What works in an sql editor does not compile in SQR:

(The 4 set of vals are identified by different account ranges),
I need the 4 opening bals followed by journal lines for all the Accounts within each Project within each department
for the period, followed by closing balances.
Ive been trying to use a Union to get the four totals, but get the following problems (it works
in wisql so i presume its the reference variables)
When i try:

 

begin-SELECT DISTINCT ON-ERROR=SQL-Error

 

A.PROJECT_ID              $P1
SUM(A.POSTED_TOTAL_AMT)   &A.POSTED_TOTAL_AMT
SUM(A.ENCUMBERED_AMOUNT)  &A.ENCUMBERED_AMOUNT

 

  Do Print-Totals
 
 
FROM PS_LEDGER A
WHERE A.BUSINESS_UNIT = 'UNSYD'
  AND A.DEPTID        = 'F001'
  AND A.ACCOUNTING_PERIOD = 9 
  AND (A.ACCOUNT BETWEEN '1800' AND '1899')
  AND A.FISCAL_YEAR   = 1998
  AND A.STATISTICS_CODE = ' '

 

UNION

 

SELECT DISTINCT
B.PROJECT_ID              $P2   
SUM(B.POSTED_TOTAL_AMT)   &B.POSTED_TOTAL_AMT2
SUM(B.ENCUMBERED_AMOUNT)  &B.ENCUMBERED_AMOUNT2

 

 Do Print-Totals

 

FROM PS_LEDGER B

 

  WHERE B.BUSINESS_UNIT = 'UNSYD'
  AND B.DEPTID        = 'F001'
  AND B.ACCOUNTING_PERIOD = 9 
  AND (B.ACCOUNT BETWEEN '2000' AND '2199')
  AND B.FISCAL_YEAR   = 1998
  AND B.STATISTICS_CODE = ' '

 

UNION   etc.......

 

ORDER BY 1

 

end-SELECT

 

I GET:

 

(SQR 5528) Sybase dbdesc: ct_results error in cursor 8:
   (107) The column prefix 'A' does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead.

 

(SQR 1303) Error in SQL (perhaps missing &name after expression):

 

If I try with:

 

begin-SELECT DISTINCT ON-ERROR=SQL-Error

 

A.PROJECT_ID              $P1
SUM(A.POSTED_TOTAL_AMT)   #A1
SUM(A.ENCUMBERED_AMOUNT)  #A2

 

!  Do Print-Totals
 
 
FROM PS_LEDGER A
WHERE A.BUSINESS_UNIT = 'UNSYD'
  AND A.DEPTID        = 'F001'
  AND A.ACCOUNTING_PERIOD = 9 
  AND (A.ACCOUNT BETWEEN '1800' AND '1899')
  AND A.FISCAL_YEAR   = 1998
  AND A.STATISTICS_CODE = ' '

 

UNION

 

SELECT DISTINCT
B.PROJECT_ID              $P2   
SUM(B.POSTED_TOTAL_AMT)   #A3
SUM(B.ENCUMBERED_AMOUNT)  #A4

 

  !Do Print-Totals

 

FROM PS_LEDGER B

 

  WHERE B.BUSINESS_UNIT = 'UNSYD'
  AND B.DEPTID        = 'F001'
  AND B.ACCOUNTING_PERIOD = 9 
  AND (B.ACCOUNT BETWEEN '2000' AND '2199')
  AND B.FISCAL_YEAR   = 1998
  AND B.STATISTICS_CODE = ' '

 

ORDER BY 1

 

end-SELECT

 

I get:

 

Error on line 259:
   (SQR 3728) SQL expression not ended, perhaps missing &Name.
FROM PS_LEDGER A

 

----------------------------------------

 

Also, Im wondering if it would be more efficient to create an array as my report
is run against 3000 DEPARTMENTS and this method I would need four Unions and then retrieve Journal Lines
for each projectid....
With an array: Would I have to Select all dept/project id and first group of totals, repeat for each range and then
sort it to be able to print headings, totals and get line details for each project......(I've never used one...)

 

Any help much appreciated!

Thanks in advance

 

Adrian Clayfield

 

 

 

========================================================================
Integrated Business Technologies Pty Ltd
Level 11   55 Hunter St Sydney  2000
Mobile: 0413 115580    Fax: 61 2 9223 4384
Email:  adclay@ibt.com.au
========================================================================