Nested case expression gives error in sql query

Question

What is wrong in this Oracle query. I am trying a nested case expression, but cant find the reason why the query fails.

SELECT USER_ID,
ADVISER_FORENAME,
ADVISER_SURNAME,
AGENT_CODE,
'[email protected]' AS EMAIL_ADDRESS,
SUBMISSION_DATE,
STATUS_CHANGED_DATE,
CASE
  WHEN (
    CASE
      WHEN TO_CHAR(TRUNC(status_changed_date), 'DY')                          IN ('fri','sat','sun')
      THEN ((TRUNC(STATUS_CHANGED_DATE)               - (TRUNC(SUBMISSION_DATE)))>4)
      ELSE ((TRUNC(STATUS_CHANGED_DATE)               - (TRUNC(SUBMISSION_DATE)))>6)
    END)
  THEN 'B'
  ELSE 'A'
END AS CATEGORY_CODE,
PLAN_REF,
PRODUCT_CODE,
CASE
  WHEN ((TRUNC(STATUS_CHANGED_DATE) - TRUNC(SUBMISSION_DATE))>4)
  THEN 'IPP'
  ELSE 'ICP'
  END AS TOUCHPOINT_NAME
 FROM PIPELINE_PLAN
 WHERE STATUS                                              =26
 AND PRODUCT_TYPE                                          =1  
 AND (TRUNC(STATUS_CHANGED_DATE) - TRUNC(SUBMISSION_DATE))<=4;

When i try running, it gives below error:

ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause:
*Action: Error at Line: 53 Column: 84

I checked for missing parenthesis but found none.

Please help.

Answer 1

Your nested CASEs are a bit too clever. In oracle, comparisons (yielding a 3-way boolean result TRUE/FALSE/UNKNOWN) and expressions (yield other kinds of values) are not interchangeable.

The result of a CASE expression is not a boolean, so it can't be used as the argument to a WHEN in an outer CASE, which requires a boolean.

You have a structure like this in your query:

CASE
  WHEN (
    CASE
      WHEN x IN ('1','2','3')
      THEN (y>4)
      ELSE (y>6)
    END)
  THEN 'B'
  ELSE 'A'
END

which needs to be rewritten as

CASE
  WHEN y > (CASE WHEN x IN ('1','2','3') THEN 4 ELSE 6 END)
  THEN 'B'
  ELSE 'A'
END

or

CASE
  WHEN (x IN ('1','2','3') AND y>4) OR (x NOT IN ('1','2','3') AND y>6)
  THEN 'B'
  ELSE 'A'
END

Answer 2

I don't see a problem with the parentheses, but you cannot use booleans as results in the then clause of the case. Perhaps this version will work:

SELECT USER_ID, ADVISER_FORENAME, ADVISER_SURNAME, AGENT_CODE,
       '[email protected]' AS EMAIL_ADDRESS, SUBMISSION_DATE, STATUS_CHANGED_DATE,
       (CASE WHEN (TRUNC(STATUS_CHANGED_DATE) - TRUNC(SUBMISSION_DATE) > 4 and 
                   TO_CHAR(TRUNC(status_changed_date), 'DY') IN ('fri','sat','sun')
                  ) or
                  (TRUNC(STATUS_CHANGED_DATE) - TRUNC(SUBMISSION_DATE) > 6 and 
                   TO_CHAR(TRUNC(status_changed_date), 'DY') not IN ('fri','sat','sun')
                  )
            THEN 'B'
            ELSE 'A'
        END) AS CATEGORY_CODE,
       PLAN_REF, PRODUCT_CODE,
      (CASE WHEN TRUNC(STATUS_CHANGED_DATE) - TRUNC(SUBMISSION_DATE) > 4
            THEN 'IPP'
            ELSE 'ICP'
       END) AS TOUCHPOINT_NAME
FROM PIPELINE_PLAN
WHERE STATUS = 26 AND PRODUCT_TYPE = 1 AND
      TRUNC(STATUS_CHANGED_DATE) - TRUNC(SUBMISSION_DATE) <= 4;

Also, based on the where clause, the condition TRUNC(STATUS_CHANGED_DATE) - TRUNC(SUBMISSION_DATE) > 6 can never be true.