Alex Rivera | Logout

sql nested case statements

Asked 2010-07-06T15:36:39.180
14

does anyone know whats wrong with this nested select statement? It complains about missing )'s but i can't understand why it doesn't work (i have left off the other bits of the statement)

Select
(CASE WHEN REQUESTS.grade_id = 1 THEN
      (CASE WHEN  ((date_completed-date_submitted)*24*60)<=30 THEN 'Yes'
           ELSE 'No'
      END)
 ELSE CASE WHEN REQUESTS.grade_id = 2 THEN
      (CASE ((date_completed-date_submitted)*24*60) <=120 THEN 'Yes'
           ELSE 'No'
      END) 
 ELSE CASE WHEN REQUESTS.grade_id = 3 THEN
     (CASE ((date_completed-date_submitted)*24*60)<=14400 THEN 'Yes'
          ELSE 'No'
     END)
 END)in_SLA

If i just do 

    Select
       (CASE WHEN REQUESTS.grade_id = 1 THEN
           (CASE WHEN  ((date_completed-date_submitted)*24*60)<=30 THEN 'Yes'
               ELSE 'No'
            END)
       END) in_sla

It works fine!

any help is much appreciated

M

sorry being a tard i'm missing the whens from the nested cases

sql oracle-database case
Edit
Report

1 Answer

30

It should be:

Select
(CASE WHEN REQUESTS.grade_id = 1 THEN
      (CASE WHEN  ((date_completed-date_submitted)*24*60)<=30 THEN 'Yes'
           ELSE 'No'
      END)
      WHEN REQUESTS.grade_id = 2 THEN
      (CASE ((date_completed-date_submitted)*24*60) <=120 THEN 'Yes'
           ELSE 'No'
      END) 
      WHEN REQUESTS.grade_id = 3 THEN
     (CASE ((date_completed-date_submitted)*24*60)<=14400 THEN 'Yes'
          ELSE 'No'
     END)
 END)in_SLA

i.e. just "WHEN" not "ELSE CASE WHEN" for each case.

I'd be tempted to simplify to:

Select
CASE WHEN (REQUESTS.grade_id = 1 AND (date_completed-date_submitted)*24*60 <= 30)
       OR (REQUESTS.grade_id = 2 AND (date_completed-date_submitted)*24*60 <=120)
       OR (REQUESTS.grade_id = 3 AND (date_completed-date_submitted)*24*60 <=14400)
     THEN 'Yes'
     ELSE 'No'
 END in_SLA
answered 2010-07-06T15:41:43.563

Your Answer