CODEWITHSUNDEEP
sqloracle-databasecase-statement
tia97
tia97

Reputation: 350

Nested Case Statement in SQL

I am getting an error stating missing right parenthesis but I cannot for the life of me figure out where in my statement.

(CASE WHEN A.AUTH_STRT_DT > CD.SVC_STRT_DT THEN 
 (CASE WHEN ABS(A.AUTH_STRT_DT - CD.SVC_STRT_DT) = 1 THEN '1 DAY BEFORE' 
      WHEN ABS(A.AUTH_STRT_DT - CD.SVC_STRT_DT) BETWEEN 2 AND 3 THEN '3 DAYS BEFORE'
      WHEN ABS(A.AUTH_STRT_DT - CD.SVC_STRT_DT) = 4 THEN '4 DAYS BEFORE'
      WHEN ABS(A.AUTH_STRT_DT - CD.SVC_STRT_DT) BETWEEN 5 AND 21 THEN '21 DAYS BEFORE'
      WHEN ABS(A.AUTH_STRT_DT - CD.SVC_STRT_DT) BETWEEN 22 AND 90 THEN '90 DAY BEFORE'
      ELSE 'OVER 90' END)  
WHEN A.AUTH_END_DT < CD.SVC_END_DT THEN 
  (CASE WHEN ABS(A.AUTH_END_DT < CD.SVC_END_DT) = 1 THEN '1 DAY AFTER' 
        WHEN ABS(A.AUTH_END_DT < CD.SVC_END_DT) BETWEEN 2 AND 3 THEN '3 DAYS AFTER'
        WHEN ABS(A.AUTH_END_DT < CD.SVC_END_DT) = 4 THEN '4 DAYS AFTER'
        WHEN ABS(A.AUTH_END_DT < CD.SVC_END_DT) BETWEEN 5 AND 21 THEN '21 DAYS AFTER'
        WHEN ABS(A.AUTH_END_DT < CD.SVC_END_DT) BETWEEN 22 AND 90 THEN '90 DAYS AFTER'
        ELSE 'OVER 90' END) 
ELSE '0 DAY' END) AS DAYSLAPSED

Upvotes: 1

Views: 3030

Answers (1)

Steven
Steven

Reputation: 13769

ABS ( x < y ) is not valid. In the second section, see the conditions replaced with a minus sign.

(CASE WHEN A.AUTH_STRT_DT > CD.SVC_STRT_DT THEN 
 (CASE WHEN ABS(A.AUTH_STRT_DT - CD.SVC_STRT_DT) = 1 THEN '1 DAY BEFORE' 
      WHEN ABS(A.AUTH_STRT_DT - CD.SVC_STRT_DT) BETWEEN 2 AND 3 THEN '3 DAYS BEFORE'
      WHEN ABS(A.AUTH_STRT_DT - CD.SVC_STRT_DT) = 4 THEN '4 DAYS BEFORE'
      WHEN ABS(A.AUTH_STRT_DT - CD.SVC_STRT_DT) BETWEEN 5 AND 21 THEN '21 DAYS BEFORE'
      WHEN ABS(A.AUTH_STRT_DT - CD.SVC_STRT_DT) BETWEEN 22 AND 90 THEN '90 DAY BEFORE'
      ELSE 'OVER 90' END)  
WHEN A.AUTH_END_DT < CD.SVC_END_DT THEN 
  (CASE WHEN ABS(A.AUTH_END_DT - CD.SVC_END_DT) = 1 THEN '1 DAY AFTER' 
        WHEN ABS(A.AUTH_END_DT - CD.SVC_END_DT) BETWEEN 2 AND 3 THEN '3 DAYS AFTER'
        WHEN ABS(A.AUTH_END_DT - CD.SVC_END_DT) = 4 THEN '4 DAYS AFTER'
        WHEN ABS(A.AUTH_END_DT - CD.SVC_END_DT) BETWEEN 5 AND 21 THEN '21 DAYS AFTER'
        WHEN ABS(A.AUTH_END_DT - CD.SVC_END_DT) BETWEEN 22 AND 90 THEN '90 DAYS AFTER'
        ELSE 'OVER 90' END) 
ELSE '0 DAY' END) AS DAYSLAPSED

Upvotes: 3

Related Questions