Solved

SQL to Oracle IIF statement

Posted on 2008-10-28
11
3,079 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 73

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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 73

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 73

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 73

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

Suggested Solutions

Title # Comments Views Activity
Can't Access My Database 57 63
SQL Insert parts by customer 12 31
Run SQL Server Proc from Access 11 28
SQL Server - Set Field Values ito Zero Based on Related Table 4 22
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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 recover a database from a user managed backup

786 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