jclem1
asked on
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.9 9,IIf([VZ_ SVC_TYPE]= "DS3+",2.9 9))) 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
I have been trying to convert the following access SQL statement to it's oracle counterpart:
IIf([VZ_SVC_TYPE]="DS0",4.
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
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
CASE WHEN mttr <= DECODE(vz_svc_type,'DS0',4
then 'Met'
else 'Missed'
end) as otd
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
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
I think sdstuber accidentally put a parenthesis after the end keyword in the case statement. Other than that, that's your answer.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Excellent sdstuber, thank you much that was my answer!
Very nice, sdstuber!
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
glad I could help
ASKER
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,'DS 3+',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
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
Case WHEN (resp_dur/60) <=decode(VZ_SVC_TYPE,'DS0'
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
Open in new window