Results 1 to 2 of 2

Thread: qry

  1. #1
    Join Date
    Sep 2002
    Posts
    17

    Unanswered: qry

    i have a doubt in this query with either of the condition it should return value
    and if it is "others" all records should return i think with decode it is possible but iam not getting with decode or case functions.
    That is either with t.dealid or t.issuerid,or t.settlementdate or others the query should return values how to chk multiple conditions in the same qry (i commented in this qry with one condition it is working fine)


    SELECT decode(i.pftype,'INST SALES','IBS','PCS SALES','PCS',i.pftype) dealertype,
    SUM(NVL(t.dollaramount,0)) amount,
    AVG(NVL(t.salesmargin,0)) salescredit
    FROM fo_cdutrading_t t,
    fo_cduissuer_m i
    WHERE t.dealerid = i.issuerid
    AND t.active = 'Y'
    AND i.pftype != 'OTHERS'

    AND t.dealid = '100027'
    --AND t.issuerid='BBUY001'
    --AND to_char(t.settlementdate,'mm/dd/yy')='08/19/02'
    AND i.pftype != 'OTHERS'
    GROUP BY i.pftype, t.salesmargin

    UNION

    SELECT decode(i.pftype,'OTHERS','Dealers',i.pftype),
    SUM(NVL(t.dollaramount,0)),
    0
    FROM fo_cdutrading_t t,
    fo_cduissuer_m i
    WHERE t.dealerid = i.issuerid
    AND t.active = 'Y'
    AND i.pftype = 'OTHERS'

    AND t.dealid = '100027'
    --AND t.issuerid='BBUY001'
    --AND to_char(t.settlementdate,'mm/dd/yy')='08/19/02'
    GROUP BY i.pftype
    ORDER BY 1

    DEALERTYPE AMOUNT SALESCREDIT
    ------------------------------ ---------- -----------
    IBS 0 5
    PCS 0 5

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: qry

    I believe this does the job, but you need to check carefully:

    SELECT decode(i.pftype,'INST SALES','IBS','PCS SALES','PCS','OTHERS','Dealers',i.pftype) dealertype,
    SUM(NVL(t.dollaramount,0)) amount,
    AVG(DECODE(i.pftype,'OTHERS',0,NVL(t.salesmargin,0 ))) salescredit
    FROM fo_cdutrading_t t,
    fo_cduissuer_m i
    WHERE t.dealerid = i.issuerid
    AND t.active = 'Y'
    AND t.dealid = '100027'
    --AND t.issuerid='BBUY001'
    --AND to_char(t.settlementdate,'mm/dd/yy')='08/19/02'
    GROUP BY i.pftype, t.salesmargin
    ORDER BY i.pftype;

Posting Permissions

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