OBIEE - Case When Statement

1 - About

This operator calculation have two type:

  • In a CASE statement, AND has precedence over OR.
  • If no ELSE expression is specified, the system will automatically add an null.

You can enter it on every place where you create a logical sql as the formula in Presentation service but also in the BI Server Expression Builder.

A Point-and-click generation of case statement logic is given by the Bins tab of the Column Formula

3 - Type of case

3.1 - Default

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

3.2 - Case If

CASE WHEN "Sales Facts"."Amount Sold" > 600000  THEN 'Big' 
     WHEN "Sales Facts"."Amount Sold" > 300000  THEN 'Middle' 
     ELSE 'Small' END

Request Condition Operator:

  • >
  • =
  • IN The IN operator can only be used in the repository and not in the logical sql (ie in an answer/analytics)

3.3 - Case Switch

It's also known as the case Lookup.

CASE  Promotions."Promo Subcategory" 
WHEN  'TV commercial'          THEN 'Commercial'  
WHEN  'TV program sponsorship' THEN 'Sponsor'  
ELSE  'Default' 
END

4 - Documentation / Reference

5 - Support

5.1 - With the GROUP system session variable in the repository

'Administrators;XMLP_ADMIN' IN ('Administrators', 'CNT_ADMIN')

5.2 - Bug

  • Bookmark "OBIEE - Case When Statement" at del.icio.us
  • Bookmark "OBIEE - Case When Statement" at Digg
  • Bookmark "OBIEE - Case When Statement" at Ask
  • Bookmark "OBIEE - Case When Statement" at Google
  • Bookmark "OBIEE - Case When Statement" at StumbleUpon
  • Bookmark "OBIEE - Case When Statement" at Technorati
  • Bookmark "OBIEE - Case When Statement" at Live Bookmarks
  • Bookmark "OBIEE - Case When Statement" at Yahoo! Myweb
  • Bookmark "OBIEE - Case When Statement" at Facebook
  • Bookmark "OBIEE - Case When Statement" at Yahoo! Bookmarks
  • Bookmark "OBIEE - Case When Statement" at Twitter
  • Bookmark "OBIEE - Case When Statement" at myAOL