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

[Dipu]

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

[DBailey]

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?

[Dipu]

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

[DBailey]

Can you post the code?

[Dipu]

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;

[art297]

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?

[DBailey · Accepted solution]

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;

[Dipu]

Thank you very much DBailey:-)

Thank you Arthur...

The Program is running now.

Merry Christmas...

[DBailey]

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.

[art297]

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).

[faizan5]

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));