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....
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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 ;
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 ENDTo 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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.