Solved

oracle case statement

Posted on 2013-12-17
6
506 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 49

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

617 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