Q

Anything similar in Oracle to Microsoft's IIF function?

Can we give a condition in SQL to get some value on satisfying that condition? We want to select in a single SQL. As we can not use condition in 'DECODE' function, unlike the 'IIF' function of Ms-ACCESS / SQL server. Is there any function or mechanism to use in the same manner as we do with the IIF function in SQL Server?

Case is is cited as follows:
Table Emp holds the employee wise scale and scale opt for. Depending on the status of the employee scale can be selecetd from scale table. Each Employee can have either of the following status as R - Regular & D - Deputationst, T - Trainee. In case of Deputationist, Scale_optfor option, which indicates eiher Parent scale (where scale_optor = 'P'i.e. Scale of Deputatinst) or Regular scale (where scale_optfor = 'R' i.e. scale for Regular employee), can be selected. Based on this selection we want to select the scale from scale table based on the following condition:

 if scale_optfor = 'P' then
   SELECT scale,basic FROM scale
   WHERE stat = emp.stat
 else
   SELECT scale,basic FROM scale
   WHERE stat = 'R'
 end if ;
There can be other status where scale_optfor option can not be selected and in those cases scale should be selected based on matching the state of Scale table and stat of emp table. Other status for Trainee = 'T'.

Table 'Emp' contains:

 stat Varchar2(1) -values -  R,D,T
 emp_cd Varchar2(5),
 scale_optfor Varchar2(1) - values P,R
 scale  Number(3).
Table 'Scale' contains two fields:
  scale  Number(3),
  Basic  Number(8,2),
  stat   Varchar2(1) - R, D, T .
This scale selection is required during the design of Emp forms where scale will be populated depending on the state of emp table and scale_optfor option.

Oracle has the CASE expression which allows you to use IF..THEN..ELSE logic in your SQL statements. The syntax is:

 CASE WHEN condition THEN
expression1 ELSE expression2 END
To demonstrate from your example:
SELECT
       CASE WHEN e.scale_optfor = 'P' THEN (SELECT scale FROM scale WHERE
stat = e.stat) END as Pscale,
       CASE WHEN e.scale_optfor = 'P' THEN (SELECT basic FROM scale WHERE
stat = e.stat) END as Pbasic,
       CASE WHEN e.scale_optfor <> 'P' THEN (SELECT scale FROM scale WHERE
stat = 'R') END as Rscale,
       CASE WHEN e.scale_optfor <> 'P' THEN (SELECT basic FROM scale WHERE
stat = 'R') END as Rbasic
  FROM emp e;
You could clean this up quite a bit to use the NVL function so that you could have a single column for scale and a single column for basic but I just wanted to show you the syntax you'll need for the CASE statement in this example. Hopefully, this will do the trick for you!

For More Information

This was first published in July 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close