Case Statement SQL Example

Case Statement SQL Example

You can use case statement inside select and put condition based on column element. In below example I will show sample select query with case statement also it has select sub query to get data from another table as well:

SELECT OE.TestOptionType,
(SELECT SR.rule_name FROM javahonk.dbo.lhs_test_rule SR WHERE SM.sm_ex_test_rule_id=SR.rule_id) as testmentType ,
CASE
        WHEN upper(SM.sm_sec_type) = 'HONK' THEN
          'Vanilla'
        WHEN upper(SM.sm_sec_type) = 'O' THEN
          'Listed'
        ELSE
          'Non-Vanilla'
        END as Category,
CASE
        WHEN OE.ExecutedBy = null THEN
          'No'
        WHEN OE.ExecutedBy <> null  THEN
          'Yes'
        ELSE
          'No'
        END as OptionExecutableTools 
FROM HONK.dbo.OptionExecutable OE 
INNER JOIN javahonk.dbo.lhs_test SM 
  ON CONVERT(int,OE.SmID) = SM.sm_id
WHERE OE.EventDate Between '20151001' and '20151031'  order by EventDate ASC

Reference:

• CASE (Transact-SQL)