Solved

SQL to Oracle IIF statement

Posted on 2008-10-28
11
3,091 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 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

821 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