Explicit cursor and group by-CodePudding

I have a select statement with a group by statement let's call it <SGB> (there are more elements in the select, but the error message refers only to the group by). The following works without error:

<SGB>;

but when I use this statement with an explicit cursor as such:

DECLARE CURSOR MMM IS 
   SGB;
BEGIN
   FOR MCR in MMM
   LOOP
     DBMS_OUTPUT.PUT_LINE('blah');
  END LOOP;
END;

I get the error message ORA-00979: not a GROUP expression

I could not find it, but is there some limitation for GROUP BY when in a cursor definition?

EDIT: here is a simplified SGB:

select 
    A, to_date(B, 'format') BDATE,
    C, D, E, F
from TB_001
pivot 
(
    MAX(VAL)
    for NAM
    in ('C' C, 'D' D, 'E' E, 'F' F)
)
group by
    A, to_date(B, 'format'), C, D, E, F
having
    to_date(B, 'format') = select( max(to_date(B, 'format')) from from TB_001 )
    and D=1
;

Another way to say it is that I have a vertical table of parameters, and parameters values for procedures, together with a date. I need to put it in a horizontal way and keep only a subset of the relevant procedures. I wanted to have a cursor so that I can in the loop exec the procedure with the relevant parameters, and potentially save an execution status in another table.

CodePudding user response：

Not really a nice solution, but I got around it by saving the select in a temporary table and making the cursor on the temporary table.

create table TEMP_T as SGB;

declare cursor MMM is 
   select * from TEMP_T;
begin
   for MCR in MMM
   loop
     DBMS_OUTPUT.PUT_LINE('blah');
   end loop;
   EXECUTE IMMEDIATE 'drop table TEMP_T';
end;

CodePudding user response：

Instead of using a temporary table can you do the below

     DECLARE CURSOR MMM IS 
       SELECT * FROM (SGB);
       BEGIN
       FOR MCR in MMM
       LOOP
         DBMS_OUTPUT.PUT_LINE('blah');
      END LOOP;
    END;