Solved

oracle case statement

Posted on 2013-12-17
6
477 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
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 31

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now