Link to home
Start Free TrialLog in
Avatar of anumoses
anumosesFlag for United States of America

asked on

oracle case statement

select drive_date,a.site_code,site_name,
       projection,actual_mobile_hrs
  from blood_drives a,sites b
 where a.site_code = b.site_code
   and drive_date = sysdate-1
   and drive_cancelled is null

DRIVE_DATE|SITE_CODE|SITE_NAME|PROJECTION|ACTUAL_MOBILE_HRS
12/16/2013|CL04|Hanover Central High School|79|6
12/16/2013|CW02|Franciscan St. Anthony Health|16|4
12/16/2013|GE38|Healthtrack Sports Wellness|17|3
12/16/2013|LI10|AT&T - Lisle|10|4
12/16/2013|NL08|Trinity Lutheran Church|18|4
12/16/2013|OP02|Rich Central High School|68|5.5
12/16/2013|PL31|C.W. Avery Family Y.M.C.A.|17|3
12/16/2013|PL48|River View Elementary School|21|4
12/16/2013|SI02|Franciscan Omni Health and Fitness|17|3.5



If actual_mobile_hrs <= 4.5 then
        CASE
         WHEN projection between 10 and 30
           THEN 3  
         WHEN projection between 31 and 60
           THEN 6
         WHEN projection between 61 and 90
           THEN 9
       WHEN projection between 91 and 120
           THEN 12
       WHEN projection between 121 and 150
           THEN 15
        End Case no_of_beds
End if;

If actual_mobile_hrs > 5 then
      CASE
         WHEN projection between 10 and 40
           THEN 3  
         WHEN projection between 11 and 75
           THEN 6
         WHEN projection between 76 and 115
           THEN 9
       WHEN projection between 116 and 150
           THEN 12
       WHEN projection between 151 and 190
           THEN 15
        End Case no_of_beds
End if;

---------------------------------------
Requirement

I want to add bed_no as a column to the select statement with the case statement. There is a if and elsif condition. How to add that to make it a case statement?

Help appreciated.
ASKER CERTIFIED SOLUTION
Avatar of MikeOM_DBA
MikeOM_DBA
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of anumoses

ASKER

Thanks
should there be a "else" for unexpected conditions? (it is prudent to do so)
e.g. a projection of less than 10 isn't handled at all
I agree with Portlet, you should always code for unexpected data.
Thanks experts. I did add else that was missing in the above code.