ORA-00934: group function is not allowed here

Question

I have written a stored procedure that I was expecting to add two count values into a collection, with the below code:

PROCEDURE generate(code_in in VARCHAR2
                 , value_1 out NUMBER
                 , value_2 out NUMBER) is
BEGIN
    SELECT 
        COUNT(CASE WHEN type = 'A' THEN 1 ELSE NULL END) INTO value_1
        , COUNT(CASE WHEN type IN ('B','D') THEN 1 ELSE NULL END) INTO value_2
    FROM table
    WHERE code = code_in;
END generate;

But when running the code I get the following error:

ORA-00934: group function is not allowed here

If I remove the second COUNT the stored procedure compiles just fine, but when I add this second line I get the error.

Can anyone help explain why this is happening? Please note that my experience with Oracle is minimal.

Can you please comment why you downvoted this question? – Radu Gheorghiu Jul 02 '15 at 08:51 — Radu Gheorghiu, Jul 02 '15 at 08:51

score 5 · Accepted Answer · answered Jul 02 '15 at 07:32

5

into is a single clause that may receive multiple variables, not a clause you append to each select item:

PROCEDURE generate(code_in in VARCHAR2
                 , value_1 out NUMBER
                 , value_2 out NUMBER) is
BEGIN
    SELECT 
        COUNT(CASE WHEN type = 'A' THEN 1 ELSE NULL END),
        COUNT(CASE WHEN type IN ('B','D') THEN 1 ELSE NULL END)
    INTO value_1, value_2 -- into clause with both variables
    FROM table
    WHERE code = code_in;
END generate;

answered Jul 02 '15 at 07:32

Mureinik

297,002
52
306
350

Excellent, thank you! Will accept your answer in a couple of minutes. – Radu Gheorghiu Jul 02 '15 at 07:36
Good one. useful – Gok Jan 30 '18 at 12:00

ORA-00934: group function is not allowed here

1 Answers1