Results 1 to 2 of 2

Thread: Decode

  1. #1
    Join Date
    Mar 2002
    Posts
    3

    Unanswered: Decode

    I am new to SQL+ and Oracle. I am trying to create a cross tabular file. It sums all the ded_code Totals, but does not sum ded_code ANN.
    My code looks like this: HELP!!
    column EMPLOYEE format 9999999999 heading 'Employee'
    COLUMN ANN FORMAT 99999999.99 heading 'Ann'
    COLUMN FOUR format 99999999.99 heading 'FOUR'
    COLUMN TOTALS format 99999999.99 heading 'Totals'
    compute sum of FOUR on employee
    compute sum of ANN on employee
    compute sum of TOTALS on employee
    SPOOL bc.TXT
    SELECT employee,
    SUM(DECODE(ded_code, 'ANN', ded_amt,0)) ANN,
    SUM(DECODE(ded_code, '4', ded_amt,0)) FOUR,
    SUM(DED_AMT)TOTALS
    FROM PAYDEDUCTN
    WHERE check_id IN (SELECT CHECK_ID
    FROM PAYMASTR
    WHERE CHECK_DATE = '22-Mar-02')
    and ded_code in ('4', '5','403F', '403X', 'ANN')
    GROUP by employee
    /

    SPOOL OUT

    The output looks like this:


    5639 .00 .00 267.05

  2. #2
    Join Date
    Mar 2002
    Location
    Paris, France
    Posts
    1

    RE : decode

    The query seems to work fine without the WHERE clause for check_id.

    SELECT employee,
    SUM(DECODE(ded_code, 'ANN', ded_amt,0)) ANN,
    SUM(DECODE(ded_code, '4', ded_amt,0)) FOUR,
    SUM(DED_AMT) TOTALS
    FROM PAYDEDUCTN
    WHERE
    ded_code in ('4', '5','403F', '403X', 'ANN')

    A you sure you have records in table PAYDEDUCTN having check_id at 22-Mar-02 for the ded_code 'ANN' ???

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •