SQL CASE Function

A CASE function requires at least one WHEN clause.

CASE Function

CASE is a complex function that has two forms; the simple-when form and the searched-when form. In either form CASE returns a result, the value of which controls the path of subsequent processing. 

>>-CASE--view--+-simple WHEN clause----+--AS------->
               |-searched WHEN clause  |        

     
                                       
>---+-ELSE NULL -------------+------END-------------------+---><
    |-ELSE result expression |         
     


simple-when-clause

|--source_value------------------------------------------------->

   .-------------------------------------------.   
   V                                           |   
>----WHEN --test_value--THEN--+-result_value-+-+----------------|
                              '-NULL---------'    


searched-when-clause

   .-------------------------------------------------.   
   V                                                 |   
|----WHEN --search_condition--THEN--+-result_value-+-+----------|
                                    '-NULL---------'

ELSE is optional. The default ELSE expression is NULL. A CASE expression is delimited by END. The test values do not have to be literal values. The searched-when form is similar, but has the additional flexibility of allowing a number of different values to be tested. 

       Exec SQL                                                
         Create Table QTemp/Temp1 As (                         
           Select DAc4, DDAlph, YR, Period, DAcct1, DAcct2,    
             DAcct3, DAcct4, DxRef1, DxRef2, DxRef3,           
             Cast(                                             
               Case When ( DAcct1 Between '41000' And '41999' )
                      Then Sum( DDValu )                       
                    Else 0                                     
                    End                                        
               As Dec( 13, 2 )) As Amount_41,                  
             Cast(                                             
               Case When ( DAcct1 Between '51000' And '51999' )
                      Then Sum( DDValu )                       
                    Else 0                                     
               End                                             
             As Dec( 13, 2 )) As Amount_51                     
           From QTemp/Temp                                   
           Group By DAc4, DDAlph, YR, Period, DAcct1, DAcct2,
             DAcct3, DAcct4, DxRef1, DxRef2, DxRef3          
           Order By DAc4, DDAlph                             
         ) Definition Only;

Note in the example, the CASE function is used inside a CAST function. The CASE WHEN is in the form of a searched clause, using an aggregate function (SUM) in the expression.

The CASE function can also be used in a SELECT statement. The example snippett demonstrates the function selecting a row column to use to supply a value based on the WHEN test. 

Report_Data As (                                           
    Select a.el1, a.el3, b.VAT,                              
       Case When ( b.federaltax = ' ' )                      
            Then b.socialSec                                 
            Else b.federaltax                                
            End As txRef1,                                   
            a.Amount                                         
        From Unmatched_Summary a                             
           Join oas_elmTaxes b                               
                 On a.el3 = b.elmcode                        
        Where a.Amount >= 600                                
                    and b.elmLevel = 3                       
                    And b.Vat In ( ' ', 'I' )                
                    And b.cmpcode = :dftcomp     
  ),