Tuesday 9 April 2013

Using case statement within SQL Query statement

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


No comments:

Post a Comment