Types of CASE Statements – OBIEE 11G

Types of CASE Statements – OBIEE 11G 
Case statements are conditional expressions that are used building blocks for that convert a value from one form to another.

  
1] CASE IF
Syntax:
CASE expr1
WHEN expr2 THEN expr3
{WHEN expr... THEN expr...}
 ELSE expr
END

2]  CASE SWITCH
Syntax:
CASE
WHEN request_condition1 THEN expr1
{WHEN request_condition2 THEN expr2}
{WHEN request_condition... THEN expr...}
ELSE expr
END 

·         In a CASE statement, AND has precedence over OR.

·         If no ELSE expression is specified, the system will automatically add an Null.

Default
If the last ELSE statement is omitted, “ELSE NULL” is assumed. The case when statement returns then NULL. 

1] Case If
CASE
WHEN "Sales Daily_Weekly_Fact"."Sales_Turnover" > 1000000  THEN ' A'   

WHEN "Sales Daily_Weekly_Fact"."Sales_Turnover" > 5000000  THEN ' B'
 ELSE 'C'
END

Request Condition Operator:

·         > 

·         =

·         IN, The IN operator can only be used in the repository and not in the logical sql (i.e in an answer/analytics)

2] Case Switch (Lookup)

CASE (Switch) this form of the CASE statement is also referred to as the CASE (Lookup)

CASE  "Sales Daily_Weekly_Fact"."Sales_Type"  

WHEN  'Internet Sales' THEN 'Online Sales' 

WHEN  'Counter Sales' THEN 'Cash Sales' 

ELSE  'Default'

END