Re: Problem with Union & Union Vs Array

[Robert Goshko <robert.goshko@AXIS-DEV.COM>]

Adrian,

 

First, don't use distinct, union all ready perform this function, you are just adding overhead, second, your selects after the union should look like regular SQL:

 

BEGIN-SELECT ON-ERROR=SQL-Error

A.PROJECT_ID              &PROGECT_ID
SUM(A.POSTED_TOTAL_AMT)   &POSTED_TOTAL_AMT
SUM(A.ENCUMBERED_AMOUNT)  &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 B.PROJECT_ID,

       SUM(B.POSTED_TOTAL_AMT),
       SUM(B.ENCUMBERED_AMOUNT)

  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...

 

All data will be returned to the column names defined in the first select, so the second, third, and fourth selects in the union just need to be regular SQL.  You don't need any "SQR" stuff in them. 

 

.....Rob

---

Robert Goshko Owner Axis Developments System Consulting Services Sherwood Park, Alberta, Canada

 

-----Original Message-----
From: Discussion of SQR, SQRIBE Technologies's database reporting language [mailto:SQR-USERS@list.iex.net]On Behalf Of Adrian Clayfield
Sent: Wednesday, October 07, 1998 2:54 AM
To: Multiple recipients of list SQR-USERS
Subject: Problem with Union & Union Vs Array

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
========================================================================