CODEWITHSUNDEEP
oracle-databasegroup-byaggregate-functions
Jake
Jake

Reputation: 604

Aggregate function in nested case statement requiring potentially messy group by

Pseudocode to describe what I'm looking at:

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 is telling me that I need to add a GROUP BY clause:

GROUP BY COLUMN_A , COLUMN_B , COLUMN_D

When I try to run this, I get an ORA-00979 (not a GROUP BY expression) error. I'm stumped here.

Upvotes: 0

Views: 699

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

You are using an aggregate function MAX(DATE) in the case statement along with other columns without any group by clause.

Assuming, you want to get the overall max date in each row, you can use 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

Upvotes: 2

Related Questions