Re: Problem with Union & Union Vs Array

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

Hi all,

Thanks for the responses. Between them Ive got a working solution.

The reason I need a Union is to accumulate different totals and move them to different areas on the report depending on their account ranges.

 

begin-SELECT ON-ERROR=SQL-Error

 

'X'        &PROJECT_ID
   if &PROJECT_ID <> $old-Project-id

     print  &PROJECT_ID    (#pline,+1,6)  on-break after=Print-Headings-and-Totals
                                                                        save=$old-Project-id                        
   end-if
'A'        &TYPE
0          &AMT1
0          &AMT2
 
   Do Accumulate-Balances

 

   Do Process-Journals

 

 FROM PS_LEDGER

 

 UNION
 SELECT
 PROJECT_ID,          
 'B',    
 SUM(POSTED_TOTAL_AMT), 
 SUM(ENCUMBERED_AMOUNT) 
 FROM PS_LEDGER
 WHERE BUSINESS_UNIT = 'UNSYD'
  AND DEPTID        = $deptid
  AND ACCOUNTING_PERIOD = #ACCOUNT_PERIOD 
  AND (ACCOUNT BETWEEN $first-cfwd-account
                      AND $last-cfwd-account)
  AND FISCAL_YEAR   = #SELECT_FISCAL_YEAR
  AND STATISTICS_CODE = ' '

 

 UNION
  SELECT
   PROJECT_ID,               
   'C',
   SUM(POSTED_TOTAL_AMT), 
   SUM(ENCUMBERED_AMOUNT)
    FROM PS_LEDGER
    WHERE BUSINESS_UNIT = 'UNSYD'
      AND DEPTID        = $deptid
      AND ACCOUNTING_PERIOD = #ACCOUNT_PERIOD 
      AND (ACCOUNT BETWEEN $first-opengrant-account
                       AND $last-opengrant-account)
      AND FISCAL_YEAR   = #SELECT_FISCAL_YEAR
      AND STATISTICS_CODE = ' '
 UNION
  SELECT
   PROJECT_ID,               
   'D',
   SUM(POSTED_TOTAL_AMT), 
   SUM(ENCUMBERED_AMOUNT)
    FROM PS_LEDGER
    WHERE BUSINESS_UNIT = 'UNSYD'
      AND DEPTID        = $deptid
      AND ACCOUNTING_PERIOD = #ACCOUNT_PERIOD 
      AND (ACCOUNT BETWEEN $first-openother-account
                       AND $last-openother-account)
      AND FISCAL_YEAR   = #SELECT_FISCAL_YEAR
      AND STATISTICS_CODE = ' '  
 UNION
  SELECT
   PROJECT_ID,               
   'E',
   SUM(POSTED_TOTAL_AMT), 
   SUM(ENCUMBERED_AMOUNT)
    FROM PS_LEDGER
    WHERE BUSINESS_UNIT = 'UNSYD'
      AND DEPTID        = $deptid
      AND ACCOUNTING_PERIOD = #ACCOUNT_PERIOD 
      AND (ACCOUNT BETWEEN $first-expense-account
                       AND $last-expense-account)
      AND FISCAL_YEAR   = #SELECT_FISCAL_YEAR
      AND STATISTICS_CODE = ' '  
 
  ORDER BY 1,2

 

end-SELECT

 

ie: all the SQR stuff is done in the first "dummy" join and the &TYPE variable is used to process the different amounts.

 

Regards

Adrian

 

 

 

 

 

 

 

 

 

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