Deal of the Day

Saturday 16 July 2011

SQL Query with CASE keyword

SQLCASEis 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.

Product Cloud

About Me

I am a techie by heart...I love to play with databases. I still remember during my masters (post Graduation) days, I use to keep notebook and pen along my bedside to capture the solution i use to get for my projects in my dream... And believe me, those design use to work without any flaw....