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 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.
This was first published in July 2002