CODEWITHSUNDEEP
oracle11g
Python code
Python code

Reputation: 11

PL/SQL: ORA-00934: group function is not allowed here

Declare

v_U_HFB_STO Varchar(200);
v_RAW_VOL_STO Number ;
v_IO_RAW_VOL_STO Number ;

 

begin

 

select B.U_BACODE||B.U_BANAME into v_U_HFB_STO, SUM(A.RAW_VOL) into v_RAW_VOL_STO, SUM(A.IO_RAW_VOL) into  v_IO_RAW_VOL_STO from UDT_SNAPSHOT_IN_TIME_REPORT A, EXT_S_ITEM B WHERE A.REORDERPOINT<>1 AND A.LOC LIKE 'STO%' AND A.ITEM=B.ITEM GROUP BY (B.U_BACODE||B.U_BANAME);
insert into STAGING.WEEKLY_ANALYSIS_TREND_REPORT_U_HFB_STO values(v_U_HFB_STO,v_RAW_VOL_STO,v_IO_RAW_VOL_STO);

 

end;
/

Try to run the code in toad but getting error like [Error] Execution (9: 49): ORA-06550: line 9, column 49:PL/SQL: ORA-00934: group function is not allowed here ORA-06550: line 9, column 1: PL/SQL: SQL Statement ignored

Upvotes: 0

Views: 298

Answers (1)

Alex Poole
Alex Poole

Reputation: 191380

Your select ... into structure isn't correct; it should have a single into:

select B.U_BACODE||B.U_BANAME, SUM(A.RAW_VOL), SUM(A.IO_RAW_VOL)
into v_U_HFB_STO, v_RAW_VOL_STO, v_IO_RAW_VOL_STO
from UDT_SNAPSHOT_IN_TIME_REPORT A, EXT_S_ITEM B
WHERE A.REORDERPOINT<>1 AND A.LOC LIKE 'STO%' AND A.ITEM=B.ITEM
GROUP BY (B.U_BACODE||B.U_BANAME);

or with modern joins (just because):

select B.U_BACODE||B.U_BANAME, SUM(A.RAW_VOL), SUM(A.IO_RAW_VOL)
into v_U_HFB_STO, v_RAW_VOL_STO, v_IO_RAW_VOL_STO
from UDT_SNAPSHOT_IN_TIME_REPORT A
join EXT_S_ITEM B ON A.ITEM=B.ITEM
WHERE A.REORDERPOINT<>1 AND A.LOC LIKE 'STO%'
GROUP BY (B.U_BACODE||B.U_BANAME);

Unless you need the variables for something else, you can skip them and do a straight insert ... select instead:

insert into STAGING.WEEKLY_ANALYSIS_TREND_REPORT_U_HFB_STO
select B.U_BACODE||B.U_BANAME, SUM(A.RAW_VOL), SUM(A.IO_RAW_VOL)
from UDT_SNAPSHOT_IN_TIME_REPORT A
join EXT_S_ITEM B ON A.ITEM=B.ITEM
WHERE A.REORDERPOINT<>1 AND A.LOC LIKE 'STO%'
GROUP BY (B.U_BACODE||B.U_BANAME);

Which you can do without PL/SQL too, of course.

Upvotes: 1

Related Questions