Solved

SQL to Oracle IIF statement

Posted on 2008-10-28
11
3,030 Views
Last Modified: 2013-12-19
Greetings experts,

I have been trying to convert the following access SQL statement to it's oracle counterpart:

IIf([VZ_SVC_TYPE]="DS0",4.99,IIf([VZ_SVC_TYPE]="DS1",3.99,IIf([VZ_SVC_TYPE]="DS3+",2.99))) AS [On Time Target], IIf([MTTR]<=[On Time Target],"Met","Missed") AS OTD

So far I am not having much luck, I am on the DECODE path but perhaps I am getting the syntax wrong.

Your help is appreciated.

Thanks
0
Comment
Question by:jclem1
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 1

Expert Comment

by:softwareservice
ID: 22823965
IIF is actually no SQL or T-SQL, but VBA. On SQL Server / T-SQL and ORACLE you have the CASE WHEN syntax:

SELECT 'column1',

       case

       when x = 1 then 'one'

       when x = 2 then 'two'

       when x = 3 then 'three' 

       else 'something else'

       end,

       'column3'

FROM ...

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22823981
DECODE(vz_svc_type,'DS0',4.99, 'DS1',3.99, 'DS3+',2.99) as on_time_target,
CASE WHEN mttr <= DECODE(vz_svc_type,'DS0',4.99, 'DS1',3.99, 'DS3+',2.99)
then 'Met'
else 'Missed'
end) as otd
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22824008
I am not certain you can reference the aliased column by name in Oracle without having to use a query of a query of pasting the entire case when statement over again, but here is what it would look like:

SELECT case when [VZ_SVC_TYPE]='DS0' then 4.99 when [VZ_SVC_TYPE]='DS1' then 3.99 when [VZ_SVC_TYPE]='DS3+' then 2.99 end AS [On Time Target]
, case when [MTTR] <= [On Time Target] then 'Met' else 'Missed' end AS OTD
0
 
LVL 31

Expert Comment

by:awking00
ID: 22824057
I think sdstuber accidentally put a parenthesis after the end keyword in the case statement. Other than that, that's your answer.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 22824139
yes, I did,  nice catch,  thanks awking00!

here is the corrected syntax....


DECODE(vz_svc_type,'DS0',4.99, 'DS1',3.99, 'DS3+',2.99) as on_time_target,
CASE WHEN mttr <= DECODE(vz_svc_type,'DS0',4.99, 'DS1',3.99, 'DS3+',2.99)
then 'Met'
else 'Missed'
end as otd
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22824195
I wasn't sure about the DECODE syntax myself, but I have seen that used to simulate what I would have done in T-SQL as CASE [VZ_SVC_TYPE] WHEN 'DS0' THEN 4.99 ... It is a much simpler switch case statement.

As sdstuber is showing, think hunch that you can't use column name defined in same query in formula, so you have to repeat the case or decode statement in his case.
0
 
LVL 1

Author Comment

by:jclem1
ID: 22824211
Excellent sdstuber, thank you much that was my answer!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22824208
Very nice, sdstuber!
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22824242
and you could do both of them with CASE if you wanted....
CASE

   WHEN vz_svc_type = 'DS0' THEN 4.99

   WHEN vz_svc_type = 'DS1' THEN 3.99

   WHEN vz_svc_type = 'DS3+' THEN 2.99

END AS on_time_target, 

CASE

   WHEN mttr <= CASE

                   WHEN vz_svc_type = 'DS0' THEN 4.99

                   WHEN vz_svc_type = 'DS1' THEN 3.99

                   WHEN vz_svc_type = 'DS3+' THEN 2.99

                END

        THEN 'Met'

        ELSE 'Missed'

END AS otd

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22824245
glad I could help
0
 
LVL 1

Author Comment

by:jclem1
ID: 22824384
My actual query for reference:

select trunc(CLOSED, 'Mon') "Month", SRC_SYS, Report#, VZ_SVC_TYPE, repo_type, Trbl_code, sum(resp_dur/60) MTTR,
decode(VZ_SVC_TYPE,'DS0',4.99,'DS1',3.99,'DS3+',2.99) On_Time_Target,
Case WHEN (resp_dur/60) <=decode(VZ_SVC_TYPE,'DS0',4.99,'DS1',3.99,'DS3+',2.99) then'Met' else 'Missed' end OTD
From mtc.MTTR_2007
where trunc(CLOSED, 'Mon') >='01-JAN-2007'
        and trunc(CLOSED)<= '31-DEC-2007'
Group by trunc(CLOSED, 'Mon'), SRC_SYS, Report#, VZ_SVC_TYPE, repo_type, Trbl_code,resp_dur    
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

708 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

18 Experts available now in Live!

Get 1:1 Help Now