Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3343
  • Last Modified:

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
0
jclem1
Asked:
jclem1
  • 4
  • 3
  • 2
  • +2
1 Solution
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now