Nested Case/If Else statements in where clause

Question

I am trying provide an alternative to the calculation I have going in my case statement. Currently it is looking at 'close_date' in order to satisfy the equation. I want it to be able to look at 'close_date' first, but if it is null, then use SYSDATE.

My code is as follows:

    SELECT CASE
               WHEN fourthlevel.case_type IN
                       ('Complaint')
               THEN
                  (SELECT COUNT (*)
                     FROM work_days1
                    WHERE     work_days1.business_date >
                                 fourthlevel.correspond_date
                          AND work_days1.business_date <=
                                 fourthlevel.close_date)

               WHEN fourthlevel.case_type IN ('Enquiry')
               THEN
                  (SELECT COUNT (*)
                     FROM work_days1
                    WHERE     work_days1.business_date >
                                 fourthlevel.create_date
                          AND work_days1.business_date <=
                                 fourthlevel.close_date)
            END
               AS sla_days
      FROM fourthlevel

So do I use a nested if-else statement in my where clauses, or another case statement?

`coalesce(fourthlevel.close_date, SYSDATE)`.. more info here: http://stackoverflow.com/questions/950084/oracle-differences-between-nvl-and-coalesce — entropic, Jul 11 '14 at 03:08

score 0 · Answer 1 · answered Aug 07 '14 at 15:33

You can use Coalesce

http://msdn.microsoft.com/en-us/library/ms190349.aspx

Coalesce checks the first parameter; if null - it uses the second parameter, else it uses the first.

COALESCE(fourthlevel.close_date, SYSDATE)

This checks fourthlevel.close_date then uses SYSDATE if the close_date is null.

Nested Case/If Else statements in where clause

1 Answers1