Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL to Oracle IIF statement

Posted on 2008-10-28
11
Medium Priority
?
3,267 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 74

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 60

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 32

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 74

Accepted Solution

by:
sdstuber earned 2000 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
 
LVL 60

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 60

Expert Comment

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

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 74

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

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!

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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 shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

971 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