SQL Problem

From: <pmohanan_at_my-deja.com>
Date: 2000/07/28
Message-ID: <8lsdh8$o0b$1_at_nnrp1.deja.com>#1/1

[Quoted] Hi..All,

   I have this SQL which generates the Report. I am facing an issue here. The report doesn't print the sum of sub_total at the end of the report.

Regards,

Praveen

CLEAR COLUMNS
    CLEAR BREAKS
    CLEAR COMPUTES     SET PAGESIZE 21

    COMPUTE SUM OF sub_total ON code1
    COMPUTE SUM OF sub_total ON comm_type_id
    COMPUTE SUM OF sub_total ON REPORT

    BREAK ON code1 SKIP 1

    column sub_total HEADING 'Total Amount' FORMAT 999,999,999.99     COLUMN code1 HEADING 'Payment Type' FORMAT a18     COLUMN sun_id HEADING 'Sun Id' FORMAT a10     COLUMN code2 HEADING 'GROSS/NET' FORMAT a5     COLUMN code3 HEADING 'LIVE/DEPOSIT' FORMAT a12     COLUMN name HEADING 'Name' FORMAT a18     COLUMN code4 HEADING 'COMMISSION TYPE' FORMAT a10     COLUMN trans_id HEADING 'Tran #' FORMAT 9999999999

    SELECT lc3.code code1, adp.payee_sun_id sun_id,

            rtrim(emp.last_name)||', '||rtrim(emp.first_name) name,
            lc1.code code2,lc2.code code3,total_amt/100 sub_total,
            charge_to ,lc4.code code4,trans_id
    FROM adptrans adp
        ,employee_all_temp emp


         ,lookup_code lc1

         ,lookup_code lc2


         ,lookup_code lc3


        ,lookup_code lc4
     WHERE (adp.amt_type_id = lc1.lookup_id AND lc1.lookup_type =
'AMOUNT_TYPE')
     AND (adp.check_type_id = lc2.lookup_id AND lc2.lookup_type =
'CHECK_TYPE')
     AND (adp.payment_id = lc3.lookup_id
        AND lc3.lookup_type = 'PAYMENT_TYPE'
        AND lc3.code != 'RELOCATION')
     AND (emp.sun_id = adp.payee_sun_id)
     AND (adp.comm_type_id (+)=lc4.lookup_id AND
lc4.lookup_type='COMMISSION_TYPE')
     ORDER BY payment_id, comm_type_id,

to_char(adp.payee_sun_id,'9999999999');

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jul 28 2000 - 00:00:00 CEST