LPAD & RPAD Implementation in OBIEE

LPAD & RPAD Implementation in OBIEE

FOR LPAD
CASE WHEN N<=LENGTH(TABLE1.COL1) THEN SUBSTRING(TABLE1.COL1 FROM 1 FOR N) ELSE CONCAT(REPEAT(' CHARACTER ', N-LENGTH(TABLE1.COL1)), TABLE1.COL1) END

THE STRING IS LEFT PADDED TO LENGTH N WITH CHARACTER.

FOR RPAD 
CASE WHEN N<=LENGTH(TABLE1.COL1) THEN SUBSTRING(TABLE1.COL1 FROM 1 FOR N) ELSE CONCAT(TABLE1.COL1, REPEAT('CHARACTER ', N-LENGTH(TABLE1.COL1)) END

THE STRING IS RIGHT PADDED TO LENGTH N WITH CHARACTER


Example LPAD& RPAD:
Suppose office shift time as follows , Shift_In:800, Shift_Out:1200 so here Shift_in time is three characters & Shift_out time is four characters. Sometime Shift_In time may be more than three characters.

Following is the way we can handle such LPAD & RPAD scenario.

CASE
WHEN LENGTH(CAST(("Dim_OFF_SHIFT_TIME"."SHIFT_IN_TIME") AS CHAR)) =3 THEN  
SUBSTRING(CAST("Dim_OFF_SHIFT_TIME"."SHIFT_IN_TIME" AS CHAR) FROM 1 FOR 1)||':'|| SUBSTRING
(CAST("Dim_OFF_SHIFT_TIME"."SHIFT_IN_TIME" AS CHAR) FROM 2 FOR 3)

WHEN LENGTH(CAST(("Dim_OFF_SHIFT_TIME"."SHIFT_IN_TIME") AS CHAR))=4 THEN  
SUBSTRING(CAST("Dim_OFF_SHIFT_TIME"."SHIFT_IN_TIME" AS CHAR) FROM 1 FOR 2)||':'|| SUBSTRING
(CAST("Dim_OFF_SHIFT_TIME"."SHIFT_IN_TIME" AS CHAR) FROM 3 FOR 4)

ELSE
CAST(("Dim_OFF_SHIFT_TIME"."SHIFT_IN_TIME") AS CHAR)
END
 

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