Results 1 to 2 of 2

Thread: Qry

  1. #1
    Join Date
    Sep 2002
    Posts
    17

    Unanswered: Qry

    with the following query iam getting an error ORA-00979 NOT A GROUP BY EXPRESSION. THIS ERROR IS COMING
    BECAUSE IAM NOT GROUPING THE T.SETTLEMENTDATE,T.ISSUERID COLUMNS .WITH OUT GROUPING THESE TWO COLUMNS HOW TO WRITE THE FOLLOWING
    QRY
    TO CHECK THE CONDITIONS DYNAMICALLY USING CASE OR DECODE

    SELECT decode(i.pftype,'INST SALES','IBS','PCS SALES','PCS','OTHERS','Dealers',i.pftype) dealertype,
    SUM(NVL(t.dollaramount,0)) amount,
    decode(i.pftype,'PCS SALES',((AVG(NVL(t.salesmargin,0))-AVG(NVL(d.origination,0)))*SUM(NVL(t.dollaramount, 0))/1000),'INST SALES',(AVG(NVL(t.salesmargin,0))*SUM(NVL(t.dollar amount,0))/1000),0) salescredit,
    decode(i.pftype,'PCS SALES',(AVG(NVL(d.origination,0))*SUM(NVL(t.dollar amount,0))/1000),0) hairut,
    AVG(NVL(d.gross,0))*SUM(NVL(t.dollaramount,0))/1000 gross,
    AVG(NVL(d.underwritermargin,0))*SUM(NVL(t.dollaram ount,0))/1000 underwritermargin,

    (case when
    t.dealid = '100027' then t.dealid
    when t.issuerid='BBUY001' then t.issuerid
    when to_char(t.settlementdate,'mm/dd/yy')='08/19/02' then
    to_char(t.settlementdate,'mm/dd/yy')
    end)

    FROM fo_cdutrading_t t,
    fo_cduissuer_m i,
    fo_cdudeal_m d
    WHERE t.dealerid = i.issuerid
    AND t.dealid = d.dealid
    AND t.active = 'Y'

    GROUP BY i.pftype, t.salesmargin, t.dealid

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

    Re: Qry

    Generally, your GROUP BY should match the non-aggregate columns in your SELECT, i.e.:

    GROUP BY decode(i.pftype,'INST SALES','IBS','PCS SALES','PCS','OTHERS','Dealers',i.pftype),
    (case when
    t.dealid = '100027' then t.dealid
    when t.issuerid='BBUY001' then t.issuerid
    when to_char(t.settlementdate,'mm/dd/yy')='08/19/02' then
    to_char(t.settlementdate,'mm/dd/yy')
    end)

    If a complex expression has a one-to-one mapping to a simpler one, then you can simplify. Here you can say:

    GROUP BY i.pftype,
    (case when
    t.dealid = '100027' then t.dealid
    when t.issuerid='BBUY001' then t.issuerid
    when to_char(t.settlementdate,'mm/dd/yy')='08/19/02' then
    to_char(t.settlementdate,'mm/dd/yy')
    end)

    ... so long as there are no i.pftype values of 'IBS','PCS' or 'Dealers'.

Posting Permissions

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