topic Re: ORA-00979: not a GROUP BY expression- Please Guide in SAS Programming

ORA-00979: not a GROUP BY expression- Please Guide

Dipu — Thu, 26 Dec 2013 16:07:47 GMT

I am getting oracle prepare error: ORA-00979: not a GROUP BY expression.
Can you guide me how can I resolve this problem?

proc sql;

connect to oracle (user=EEEEEE password=RRRRRR path='werwrw' buffsize=1000000);

create table R1.TT as select * from connection to oracle

(Select household_id

,business_unit

,Transaction_date

,Order_id

,sales_credit

,sum(decode(sales_Credit,1,amount,2,-amount)) as Amount

,sum(decode(sales_Credit,1,quantity,2, -quantity)) as Quantity

,Label2_Code

,nvl/*coalesce*/ (case transaction_type*sales_credit when 1 then profit_amount when 2 then -profit_amount end,0)as net_Profit_amount

from mbs.transaction where business_unit in (1,3)

and sales_credit in (1,2)

group by household_id

,business_unit

,Transaction_date

,Order_id

,sales_credit);

disconnect from oracle ;

quit;

Thanks

Re: ORA-00979: not a GROUP BY expression- Please Guide

DBailey — Thu, 26 Dec 2013 16:16:09 GMT

how do you want the label2_code and net profit amount to be included? probably a sum around the net profit amount and include label2_code in the group by?

Re: ORA-00979: not a GROUP BY expression- Please Guide

art297 — Thu, 26 Dec 2013 16:22:25 GMT

I'd think you have to include all of the non-aggregated fields in the group by statement (i.e., also include Label2_Code and net_Profit_amount).

Re: ORA-00979: not a GROUP BY expression- Please Guide

Dipu — Thu, 26 Dec 2013 16:32:17 GMT

Hi DBailey,

I am including both of them as field or variable in my table. (both are different variables)
label2_code

net profit amount

Thank you

hi Arthur

I included both of them in - group by

but it is still showing the same error.

Thank you

Re: ORA-00979: not a GROUP BY expression- Please Guide

DBailey — Thu, 26 Dec 2013 16:44:21 GMT

Can you post the code?

Re: ORA-00979: not a GROUP BY expression- Please Guide

Dipu — Thu, 26 Dec 2013 16:47:12 GMT

Yees sure

proc sql;

connect to oracle (user=EEEEEE password=RRRRRR path='werwrw' buffsize=1000000);

create table R1.TT as select * from connection to oracle

(Select household_id

,business_unit

,Transaction_date

,Order_id

,sales_credit

,sum(decode(sales_Credit,1,amount,2,-amount)) as Amount

,sum(decode(sales_Credit,1,quantity,2, -quantity)) as Quantity

,Label2_Code

,nvl/*coalesce*/ (case transaction_type*sales_credit when 1 then profit_amount when 2 then -profit_amount end,0)as net_Profit_amount

from mbs.transaction

where sales_credit in (1,2)

and business_unit in (1,3)and household_id>0

group by household_id

,business_unit

,Transaction_date

,Order_id

,sales_credit

,Label2_Code

,net_profit_amount);

disconnect from oracle ;

quit;

Re: ORA-00979: not a GROUP BY expression- Please Guide

art297 — Thu, 26 Dec 2013 17:44:51 GMT

I just noticed 2 things. One: you should also add nvl to the group by statement. Two, shouldn't there be a comma after nvl?

Re: ORA-00979: not a GROUP BY expression- Please Guide

DBailey — Thu, 26 Dec 2013 17:54:27 GMT

I don't think oracle gas the functionality to group by a calculated named expression...you need to include the nvl statement in the group by rather than the calculated column name net_profit_amount. I haven't seen a comment in the middle of a function like you have in the NVL, so if this still doesn't work you might try removing that.

Re: ORA-00979: not a GROUP BY expression- Please Guide

DBailey — Thu, 26 Dec 2013 18:01:44 GMT

proc sql;

connect to oracle (user=lookup password=lookup path='K6P_POLE' buffsize=1000000);

create table R1.TT as select * from connection to oracle

(Select household_id

,business_unit

,Transaction_date

,Order_id

,sales_credit

,sum(decode(sales_Credit,1,amount,2,-amount)) as Amount

,sum(decode(sales_Credit,1,quantity,2, -quantity)) as Quantity

,Label2_Code

,nvl(case transaction_type*sales_credit when 1 then profit_amount when 2 then -profit_amount end,0)as net_Profit_amount

from mbs.transaction

where sales_credit in (1,2)

and business_unit in (1,3)and household_id>0

group by household_id

,business_unit

,Transaction_date

,Order_id

,sales_credit

,Label2_Code

,nvl(case transaction_type*sales_credit when 1 then profit_amount when 2 then -profit_amount end,0)

);

disconnect from oracle ;

quit;

Re: ORA-00979: not a GROUP BY expression- Please Guide

Dipu — Thu, 26 Dec 2013 18:08:31 GMT

Thank you very much DBailey:-)

Thank you Arthur...

The Program is running now.

Merry Christmas...

Re: ORA-00979: not a GROUP BY expression- Please Guide

faizan5 — Wed, 16 Aug 2017 19:16:30 GMT

working in 10g but not in 12c

SELECT *
FROM ( SELECT ABS (SUM (DECODE (CM, 5, D.MPRef.mass, 0.0))) "Calculated",
                 DECODE (GROUPING_ID (S1),
                         1, '*** Grand Total ***',
                         0, (SELECT title
                               FROM TEST_979_title T
                              WHERE TitleSubfile = '33' AND T.code = S1))
                    title,
                 S1
            FROM (WITH FindAssemblies
                       AS (SELECT AssemblyModelCode "M",
                                  AssemblyPartnum "Assembly"
                             FROM TEST_979_effect E
                            WHERE     AssemblyModelCode = 'N'
                                  AND ('123' BETWEEN E.firstEffect
                                                       AND E.lastEffect))
                  SELECT MPRef, ConsideredMaturity CM, NVL (LOADCODE, '-') S1
                    FROM TEST_979_Detail
                   WHERE     AssemblyModelCode = 'N'
                         AND AssemblyPartNum IN (SELECT "Assembly"
                                                   FROM FindAssemblies)) D
        GROUP BY ROLLUP (S1));