Deal of the Day

Saturday 16 July 2011

SQL Query with CASE keyword

SQL example CASE WHEN

SQL CASE is a very unique conditional statement providing if/then/else logic for any ordinary SQL command. It then provides when-then-else functionality (WHEN this condition is met THEN do_this).
CASE can be used with both SELECT Clause and WHERE Clause
Select Name,DOB,
        Case
          When Substr( Digits( DOB ), 4, 2 ) = '01' Then 'JAN'
          When Substr( Digits( DOB ), 4, 2 ) = '02' Then 'FEB'
          When Substr( Digits( DOB ), 4, 2 ) = '03' Then 'MAR'
          When Substr( Digits( DOB ), 4, 2 ) = '04' Then 'APR'
          When Substr( Digits( DOB ), 4, 2 ) = '05' Then 'MAY'
          When Substr( Digits( DOB ), 4, 2 ) = '06' Then 'JUN'
          When Substr( Digits( DOB ), 4, 2 ) = '07' Then 'JUL'
          When Substr( Digits( DOB ), 4, 2 ) = '08' Then 'AUG'
          When Substr( Digits( DOB ), 4, 2 ) = '09' Then 'SEP'
          When Substr( Digits( DOB ), 4, 2 ) = '10' Then 'OCT'
          When Substr( Digits( DOB ), 4, 2 ) = '11' Then 'NOV'
          When Substr( Digits( DOB ), 4, 2 ) = '12' Then 'DEC'
        End , DeptMst.DeptManager
   From EmpData, DeptMst
Where DeptMst.Dept =
        Case
          When Substr( EMPID , 1, 1 ) = 'P' Then 'Purchase'
          When Substr( EMPID , 1, 1 ) = 'Q' Then 'Quality'
          Else ‘Sale’
          End

The above query will provide data from Employee Data file and Department Master File. It will have 4 columns as Employee Name, Date of Birth, Month of Birth and Department Manager’s Name. The query gets department Id from first Character of Employee ID.

Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.

Happy Learning

1 comment:

  1. fpmora@yahoo.com4 February 2012 2:12 am

    substr('JanFebMarAprMayJunJulAugSepOctNovDec',
    locate(':'||'10',
    ':01:02:03:04:05:06:07:08:09:10:11:12'),3)

    ReplyDelete