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

Question

这有效:

 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)

但是试图让 case 语句做一个 IN 语句(在这里尝试一个更动态的 sql)，下面的代码导致 ORA-00979 错误。

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)

是否有可能使这项工作或有其他选择？谢谢。
——乔纳斯

Benoit :这是一个基于您的 sql 的修改后的 sql，它重新创建了错误:

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

基本上缺少的是 FROM 表应该有多个值，并且在 CASE 语句中引用了一列。

Answer 1

我无法通过以下请求(工作)重现此错误:

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

尝试:

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"