Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

oracle case statement

Posted on 2013-12-17
6
Medium Priority
?
516 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 500 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 500 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

715 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