伪代码来描述我在看什么:
SELECT
COLUMN_A
, COLUMN_B
, CASE WHEN (OUTER_CONDITION_A = TRUE) THEN
CASE WHEN (INNER_CONDITION_1 = TRUE) THEN 'Choice 1'
WHEN (INNER_CONDITION_2 = TRUE) THEN 'Choice 2'
WHEN (INNER_CONDITION_3 = TRUE) THEN 'Choice 3'
ELSE 'Other Choice' END
ELSE CASE WHEN (OUTER_CONDITION_B = TRUE) THEN
CASE WHEN (INNER_CONDITION_4 = TRUE) THEN 'Choice 4'
WHEN (INNER_CONDITION_5 = TRUE) THEN 'Choice 5'
WHEN (INNER_CONDITION_6 = TRUE) THEN 'Choice 6'
ELSE TO_CHAR(MAX(DATE)) END
END AS 'COLUMN_C'
, COLUMN_D
FROM TABLE_A
Oracle SQL Developer告诉我,我需要添加GROUP BY子句:
GROUP BY COLUMN_A,COLUMN_B,COLUMN_D
当我尝试运行此命令时,出现ORA-00979(不是GROUP BY表达式)错误。我被困在这里。
您正在MAX(DATE)
case语句中使用聚合函数以及其他没有任何group by子句的列。
假设您要获取每一行的总体最大日期,可以使用analytic MAX(DATE) OVER ()
:
SELECT
COLUMN_A
, COLUMN_B
, CASE WHEN (OUTER_CONDITION_A = TRUE) THEN
CASE WHEN (INNER_CONDITION_1 = TRUE) THEN 'Choice 1'
WHEN (INNER_CONDITION_2 = TRUE) THEN 'Choice 2'
WHEN (INNER_CONDITION_3 = TRUE) THEN 'Choice 3'
ELSE 'Other Choice' END
ELSE CASE WHEN (OUTER_CONDITION_B = TRUE) THEN
CASE WHEN (INNER_CONDITION_4 = TRUE) THEN 'Choice 4'
WHEN (INNER_CONDITION_5 = TRUE) THEN 'Choice 5'
WHEN (INNER_CONDITION_6 = TRUE) THEN 'Choice 6'
ELSE TO_CHAR(MAX(DATE) OVER ()) END
END AS 'COLUMN_C'
, COLUMN_D
FROM TABLE_A
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句