BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dipu
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
DBailey
Lapis Lazuli | Level 10

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;

View solution in original post

10 REPLIES 10
DBailey
Lapis Lazuli | Level 10

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
Calcite | Level 5


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
Lapis Lazuli | Level 10

Can you post the code?

Dipu
Calcite | Level 5

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
Opal | Level 21

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

Thank you very much DBailey:-)

Thank you Arthur...

The Program is running now.

Merry Christmas...

DBailey
Lapis Lazuli | Level 10

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
Opal | Level 21

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 14998 views
  • 11 likes
  • 4 in conversation