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.
LVL 6
anumosesAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
MikeOM_DBAConnect With a Mentor Commented:
Just add it to the select:
SELECT drive_date
       , a.site_code
       , site_name
       , projection
       , actual_mobile_hrs
       , CASE 
           WHEN 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 
           WHEN 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 
        END AS no_of_beds
  FROM blood_drives a, sites b
 WHERE a.site_code = b.site_code
   AND drive_date = SYSDATE - 1
   AND drive_cancelled IS NULL;

Open in new window

0
 
awking00Connect With a Mentor Commented:
I assume between 11 and 75 is a typo and should be between 41 and 75
CASE WHEN actual_mobile_hrs <= 4.5 and projection between 10 and 30 then 3
     WHEN actual_mobile_hrs <= 4.5 and projection between 31 and 60 then 6
     WHEN actual_mobile_hrs <= 4.5 and projection between 61 and 90 then 9
     WHEN actual_mobile_hrs <= 4.5 and projection between 91 and 120 then 12
     WHEN actual_mobile_hrs <= 4.5 and projection between 121 and 150 then 15
     WHEN actual_mobile_hrs > 5 and projection between 10 and 40 then 3
     WHEN actual_mobile_hrs > 5 and projection between 41 and 75 then 6
     WHEN actual_mobile_hrs > 5 and projection between 76 and 115 then 9
     WHEN actual_mobile_hrs > 5 and projection between 116 and 150 then 12
     WHEN actual_mobile_hrs > 5 and projection between 151 and 190 then 15
END no_of_beds
0
 
anumosesAuthor Commented:
Thanks
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
PaulCommented:
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
0
 
MikeOM_DBACommented:
I agree with Portlet, you should always code for unexpected data.
0
 
anumosesAuthor Commented:
Thanks experts. I did add else that was missing in the above code.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.