SQL-Case

Background

• It allows you to build some application logic based an all columns of the current row.
• It allows you to aggregate (group by) on values which are derived from some logic

Syntax

 CASE [ expression ]
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   WHEN condition_3 THEN result_3
   ...
   WHEN condition_n THEN result_n
   ELSE result
 END

 There are two forms one where the a columns is compared to values in the when clause

  WHEN 'SYS' THEN 'The owner is SYS'
  WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
  ELSE 'The owner is another value'

 select a, 
 case  
  when  a >= 3 then 'Three'
  when  a >= 2 then 'Two'
  when  a >= 1 then 'One'
 end
 from 
 (
 select 1 a from dual union all
 select 2 a from dual union all
 select 3 a from dual 
 )

 1	One
 2	Two
 3	Three

Samples

  with exp as
  ( 
  select 
  case  
       when expiration_date  <  to_date('01.10.2014', 'dd.mm.yyyy') then 'New Contracts before vor 01.10.2014'
       when expiration_date  <  to_date('01.01.2015', 'dd.mm.yyyy') then 'New Contracts from ab 01.10.2014'
       when expiration_date  <  to_date('01.04.2015', 'dd.mm.yyyy') then 'New Contracts from ab 01.01.2015'       
       when expiration_date  <  to_date('01.07.2015', 'dd.mm.yyyy') then 'New Contracts from ab 01.04.2015' 
       when expiration_date  >= to_date('01.07.2015', 'dd.mm.yyyy') then 'New Contracts from ab 01.07.2015'
  end as period  
  , count(*) as Num
 from contracts
 )
 select period, count(*) 
 from exp
 group by period
 order by period ;

• This statement builds a statistic how many contracts expire in a certain period
• The purpose of the with statement is to have an easy, and readable way to specify group by'' and ''order by

Build English correct output of a list connected with and

 select 
    option || case rownum <> 1 then ' and ' end as Options; 
 from option;

  Pepperoni and
  Cheese and
  Tomato