OBIEE11g: Replacing a "NUMBER" datatype with NULL ( ' ' ) in a case statement

Hi All,

We have a requirement where we need to replace a NUMBER data type with NULL value (blank value). We have IFNULL function to replace a NULL value. But in this scenario we have to replace a number data type value with a character ( Null value is considered as character in Obiee). To achieve this we have done a simple manipulation using a case statement.

The following example will illustrates the workaround....

Scenario : I have a number data type column Calendar Year . When the Year is greater than 2009 then it has to display the value eg : 2010 ,2011 etc.. otherwise it has to display null.

Step 1 : Add the column to the Criteria tab.

Step 2 : Click on the Results tab and see the results.

Step 3 : Now you can see its retuning data which is less than 2009 also. So i need to replace the values which are less than 2009 with a blank. Go back to the criteria tab and click on the column formula and write the formula as shown in the below screen shot.

Syntax : Case when "CONDITION" then "VALUE" end

Step 4 : Now go to the Results tab you can see the expected result.

Conclusion : Here we are just taking leverage on the case statement functionality. If we exclude the "Else"part in a case statement then it would consider null as (blank value) default value. It wont bother about the data type of the first argument. :)

Hope this helps you......