Re: Unions in SQR Select Statements

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

I recently used this Union to do a similar thing:
You can use &TYPE to determine which range youre dealing with.
The first "bucket" is a dummy one and contains all the SQR.

begin-SELECT -XP ON-ERROR=SQL-Error

'X'        &PROJECT_ID
'A'        &TYPE
0          &AMT1
0          &AMT2

   let $Project-Id = &PROJECT_ID

   Do Accumulate-Balances

   if $Project-Id <> 'X'
     if &TYPE = 'E'
       let $jrnl_lns = 'N'
       do Process-Journals
       if $jrnl_lns = 'N'
         do Initialize-Totals
       end-if
     end-if
   end-if


 FROM PS_SYD_LEDGER_VW

 UNION
 SELECT
 PROJECT_ID,
 'B',
 POSTED_TOTAL_AMT,
 ENCUMBERED_AMOUNT
 FROM PS_SYD_LEDGER_VW
 WHERE BUSINESS_UNIT = $Select_Business_unit
  AND DEPTID        = $Deptid
  AND (ACCOUNTING_PERIOD BETWEEN #SELECT_BEGIN_PERIOD
                            AND #SELECT_END_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',
   POSTED_TOTAL_AMT,
   ENCUMBERED_AMOUNT
    FROM PS_SYD_LEDGER_VW
    WHERE BUSINESS_UNIT = $Select_Business_unit
      AND DEPTID        = $Deptid
      AND (ACCOUNTING_PERIOD BETWEEN #SELECT_BEGIN_PERIOD
                                AND #SELECT_END_PERIOD)
      AND (ACCOUNT BETWEEN $first-opengrant-account
                       AND $last-opengrant-account)
      AND FISCAL_YEAR   = #SELECT_FISCAL_YEAR
      AND STATISTICS_CODE = ' '
 UNION
  SELECT


........etc

  ORDER BY 1,2




-----Original Message-----
From: John Munkberg (Connexus) <Johnm@CONNEXUSENERGY.COM>
To: Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
Date: Wednesday, October 28, 1998 6:57 AM
Subject: Unions in SQR Select Statements


>Is is possible to use UNIONS in SQR?   (Oracle 7.3 DB)
>
>I want to do something like this (forgive the syntax, that's my
>question):
>
>BEGIN-SELECT
>empno
>  DO results
>  from employee
>  UNION
>empno
>  DO results
>  from supervisors
>END-SELECT
>
>I would then feed each distinct empno into another procedure (results)
>to collect data and print the results.
>
>I know I could create a view from which to select all the empnos, but
>then there is maintenance, etc.
>
>
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>~~~~~~~~~
>|   John E. Munkberg    |   CONNEXUS  ENERGY   |
>JohnM@CONNEXUSENERGY.com     |
>|     612.323.2782      |      Ramsey, MN     |
>http://WWW.CONNEXUSENERGY.com  |
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>~~~~~~~~~
>