starting in Oracle 9i,You can use the case statement in sql query statement,It has the functionality of
IF-THEN-ELSE statement
Syntax
CASE < expression >
when <condition_1> then <result_1>
when <condition_2> then <result_2>
......
when <condition_n> then <result_n>
else result
END
Example:
select empno,
ename,
sal,
case
when emp.comm is null then 0
else emp.comm
end as comm,
deptno
from
emp;
this case function placed 0 where comm column having null value in emp table
----------------------------------
You can also return a value based on combination of two columns see the below example
select supplier_id
case
when supplier_name='IBM' and supplier_type='HARDWARE' then 'North Office'
when supplier_name='IBM' and supplier_type='SOFTWARE' then 'South Office'
End
from suppliers;
here supplier_name and supplier_type are different columns.
and here not used ELSE clause,it is possible to omit
if you can omit else clause in case statement it will return null value,wherever it find wrong result
IF-THEN-ELSE statement
Syntax
CASE < expression >
when <condition_1> then <result_1>
when <condition_2> then <result_2>
......
when <condition_n> then <result_n>
else result
END
Example:
select empno,
ename,
sal,
case
when emp.comm is null then 0
else emp.comm
end as comm,
deptno
from
emp;
this case function placed 0 where comm column having null value in emp table
----------------------------------
You can also return a value based on combination of two columns see the below example
select supplier_id
case
when supplier_name='IBM' and supplier_type='HARDWARE' then 'North Office'
when supplier_name='IBM' and supplier_type='SOFTWARE' then 'South Office'
End
from suppliers;
here supplier_name and supplier_type are different columns.
and here not used ELSE clause,it is possible to omit
if you can omit else clause in case statement it will return null value,wherever it find wrong result
No comments:
Post a Comment