Problem solve Get help with specific problems with your technologies, process and projects.

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
   SELECT scale,basic FROM scale
   WHERE stat = 'R'
 end if ;

Table 'Emp' contains:

 stat Varchar2(1) -values -  R,D,T
 emp_cd Varchar2(5),
 scale_optfor Varchar2(1) - values P,R
 scale  Number(3).
  scale  Number(3),
  Basic  Number(8,2),
  stat   Varchar2(1) - R, D, T .

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

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 'Scale' contains two fields: 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.

Dig Deeper on Oracle and SQL