Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sql nested case statements

Tags:

sql

case

oracle

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

like image 270
matt1234 Avatar asked Jul 06 '10 15:07

matt1234


People also ask

Can you have nested CASE statements in SQL?

CASE can be nested in another CASE as well as in another IF…ELSE statement. In addition to SELECT, CASE can be used with another SQL clause like UPDATE, ORDER BY.

Can we use two case in SQL?

SQL case statement with multiple conditions is known as the Search case statement. So, You should use its syntax if you want to get the result based upon different conditions -. END AS [ALIAS_NAME];

Can you have multiple conditions in a case statement?

Multiple conditions in CASE statementYou can evaluate multiple conditions in the CASE statement.


1 Answers

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
like image 85
Tony Andrews Avatar answered Nov 03 '22 00:11

Tony Andrews