Re: Use SQL instead of RPG?

["Mike the Code Monkey" <koldark@xxxxxxxxx>]

Here is an example of a view with a union and a sum I have trimmed for
length.

CREATE VIEW LAWCDB.CUVROCNIBT (
   COMPANY , FISCAL_YEAR FOR COLUMN FISCA00001 ,  PERIOD ,  BALANCE )
   AS  SELECT
    COMPANY AS COMPANY,  FISCAL_YEAR AS FISCAL_YEAR,
    1 AS PERIOD,  SUM(( DB_AMOUNT_01 + CR_AMOUNT_01 ) * - 1 ) AS BALANCE
    FROM
    LAWAPP81DB.DBGLGAM
    WHERE
    ACCOUNT BETWEEN 40000 AND 87999
    GROUP BY
    COMPANY,
    FISCAL_YEAR
    UNION
    SELECT
    COMPANY AS COMPANY,
    FISCAL_YEAR AS FISCAL_YEAR,
    2 AS PERIOD,
    SUM(( DB_AMOUNT_02 + CR_AMOUNT_02 ) * - 1 ) AS BALANCE
    FROM
    LAWAPP81DB.DBGLGAM
    WHERE
    ACCOUNT BETWEEN 40000 AND 87999
    GROUP BY
    COMPANY,
    FISCAL_YEAR ;


On 5/18/06, steema@xxxxxxxxxxxxx <steema@xxxxxxxxxxxxx> wrote:
> Mike, Whereever I put SUM(myfield), it gives a message that it is not
> allowed w/ UNION.
> Steve