Encountering ORA-00979: not a GROUP BY expression when using CASE - IN statements in sql

Question

This works:

 SELECT (CASE
             WHEN x = 'value' THEN
              a.col1
             ELSE
              nvl(a.col1, a.col2)
           END)
      FROM table1 a
     WHERE a.this = 'that'
     GROUP BY (CASE
                WHEN x = 'value'  THEN
                 a.col1
                ELSE
                 nvl(a.col1, a.col2)
              END)

But trying to have the case statement do an IN statement(trying for a more dynamic sql here), the following code results to an ORA-00979 error.

SELECT (CASE
         WHEN x IN (SELECT me FROM here WHERE this = 'example') THEN
          a.col1
         ELSE
          nvl(a.col1, a.col2)
       END)
  FROM table1 a
 WHERE a.this = 'that'
 GROUP BY (CASE
            WHEN x IN (SELECT me FROM here WHERE this = 'example') THEN
             a.col1
            ELSE
             nvl(a.col1, a.col2)
          END)

Is it possible to make this work or have an alternative? Thanks. --Jonas

Benoit: Here's a modified sql based on your sql that recreates the error:

select (case when a.y IN (select 'A'||ROWNUM from dual where rownum=1) then 1 else 0 end)
from (SELECT 'A'||ROWNUM y, 'B' x FROM DUAL CONNECT BY ROWNUM <= 3) a where x = 'B'
group by (case when a.y IN (select 'A'||ROWNUM from dual where rownum=1) then 1 else 0 end)
;

Basically what was missing was that the FROM table should have more than one values and that a column was referenced in the CASE statement.

Answer 1

Hello, I cannot reproduce this error with following request (working):

select (case when 'X' IN (select dummy from dual where rownum=1) then 1 else 0 end)
from dual
where dummy = 'X'
group by (case when 'X' IN (select dummy from dual where rownum=1) then 1 else 0 end)
;

Try:

WITH table1_extended AS (
   SELECT a.*, CASE WHEN x IN .... END "condition"
     FROM table1 a
)
SELECT b."condition"
  FROM table1_extended b
 WHERE b.this = 'that'
 GROUP BY b."condition"

Answer 2

Is there a reason you can't move the subselect into a join? From your adjusted version on @Benoit's test case you could do:

select case when a.y = b.z then 1 else 0 end, count(*)
from (select 'A'||rownum y, 'B' x from dual connect by rownum <= 3) a,
    (select 'A'||rownum z from dual where rownum=1) b
where a.x = 'B'
group by case when a.y = b.z then 1 else 0 end;

Which gives (in 10g):

CASEWHENA.Y=B.ZTHEN1ELSE0END COUNT(*)               
---------------------------- ---------------------- 
1                            1                      
0                            2

Not entirely convinced that will give the answer you want, but hard to tell as it's so simplified, and might be a starting point.

---

Edit Seems this is indeed too simplistic. Another possible solution that also looks much too simple but might do the trick is to approach this from the other end and just make the case in the select an aggregate function:

SELECT MIN(CASE
         WHEN x IN (SELECT me FROM here WHERE this = 'example') THEN
          a.col1
         ELSE
          nvl(a.col1, a.col2)
       END)
  FROM table1 a
 WHERE a.this = 'that'
 GROUP BY (CASE
            WHEN x IN (SELECT me FROM here WHERE this = 'example') THEN
             a.col1
            ELSE
             nvl(a.col1, a.col2)
          END)