SQL to Oracle IIF statement

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
LVL 1
jclem1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

softwareserviceCommented:
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
sdstuberCommented:
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
Kevin CrossChief Technology OfficerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

awking00Commented:
I think sdstuber accidentally put a parenthesis after the end keyword in the case statement. Other than that, that's your answer.
0
sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
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
jclem1Author Commented:
Excellent sdstuber, thank you much that was my answer!
0
Kevin CrossChief Technology OfficerCommented:
Very nice, sdstuber!
0
sdstuberCommented:
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
sdstuberCommented:
glad I could help
0
jclem1Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.