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