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
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;
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?
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
Can you post the code?
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;
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?
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;
Thank you very much DBailey:-)
Thank you Arthur...
The Program is running now.
Merry Christmas...
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.
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).
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));
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.