anumoses
asked on
oracle case statement
select drive_date,a.site_code,sit e_name,
projection,actual_mobile_h rs
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|PROJE CTION|ACTU AL_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|Healthtrac k 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.
projection,actual_mobile_h
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_
12/16/2013|CL04|Hanover Central High School|79|6
12/16/2013|CW02|Franciscan
12/16/2013|GE38|Healthtrac
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
e.g. a projection of less than 10 isn't handled at all
I agree with Portlet, you should always code for unexpected data.
ASKER
Thanks experts. I did add else that was missing in the above code.
ASKER