Contents

Background

Many novice developers are not aware what amazing things can be archived with SQL very efficiently saving you from long writing long blocks of lengthly classic code. The case statement is one of the magical statements.

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

The case statement evaluates each condition from to to button. When the condition matches it then the value given by then and terminates (short circut). If no condition matches the else clause is evaluated.
 There are two forms one where the a columns is compared to values in the when clause

SELECT table_name, CASE owner
  WHEN 'SYS' THEN 'The owner is SYS'
  WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
  ELSE 'The owner is another value'
END FROM all_tables;
and another when an expression is used in the when clause.

 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 
 )

results in
 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 ;


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

Status: Published Date: 2014-11-30 12:48:31 +0100 (So, 30 Nov 2014) Revision: 32

Copyright bei Andreas Haack (C) 2014. Diese Seite wird so wie sie ist zur Verfuegung gestellt, ohne irgenweche Garantien der Verwendbarkeit fuer bestimte Zwecke. Die auf dieser Seiten angebrachten Links liegen ausserhalb der redaktionellen Verantwortung von Andreas Haack und es wird keine Haftung oder Garantie uebernommen. Die Seiten sind Copyright (c) 2014 von Andreas Haack. Kein Teil darf ohne die schriftliche Einverstaendnis von Andreas Haack veroeffentlicht werden.
The page is provided 'as is' , without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fit- ness for a particular purpose and non-infringement. In no event shall Andreas Haack be liable for any claim, damages or other liability. This page is copyrighted property of Andreas Haack. Copyright by Andreas Haack (c) 2014 . No part of this page may be published without written permission for Andreas Haack. A hyper-link may created to this page but NOT to the embedded elements of this page. It may be freely downloaded for private purpose only as long as it is unaltered.