Solved

oracle case statement

Posted on 2013-12-17
6
497 Views
Last Modified: 2013-12-18
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.
0
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 125 total points
ID: 39724812
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 125 total points
ID: 39724858
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
 
LVL 6

Author Closing Comment

by:anumoses
ID: 39724996
Thanks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39725918
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39726737
I agree with Portlet, you should always code for unexpected data.
0
 
LVL 6

Author Comment

by:anumoses
ID: 39726750
Thanks experts. I did add else that was missing in the above code.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question