Nested Case Statement Help

Mik A.M.

Hello all,

I'm hoping you can help out.  I've searched the board for something related to my problem and couldn't find anything.

So here goes:

I'm trying to create a column in an analysis whose values are based on a case statement that's comparing 2 column values.  So for instance:

Column3

case

when (column1 = A and column2 = 1 then Value1)

when (column1 = B and column2 = 2 then Value2)

END

To add to the complexity, column1 and column2 are case statements themselves based on their own values.

Any way to do this within OBIEE 11g?  I don't have back-end access so I'm unable to make modifications/create new columns on the backend.  I'm trying to avoid engaging the devs as their turnaround time is too long.

Any assistance would be greatly appreciated.

Gianni Ceresa

OBIEE accept a CASE WHEN statement, so just write it right and you are done :

CASE WHEN "tab1"."col1" = 'A' AND "tab2"."col1" = 1 THEN 'Value 1'

WHEN "tab1"."col1" = 'B' AND "tab2"."col1" = 2 THEN 'Value 2'

END

Your columns are CASE WHEN ? Not a problem ...

CASE WHEN CASE WHEN "tab1"."col1" = 'A1' THEN 'A' ELSE 'B' END = 'A' AND "tab2"."col1" = 1 THEN 'Value 1'

WHEN CASE WHEN "tab1"."col1" = 'A1' THEN 'A' ELSE 'B' END = 'B' AND "tab2"."col1" = 2 THEN 'Value 2'

END

Just make sure your syntax is right, that each CASE is closed by a END and no problem at all ...

What is the problem? Did you try to write the CASE statement?

Bose Pandian

Hi Mik A.M,

Try with the same syntax you wrote. It will work both in RPD and Answer Side.

Mik A.M.

Thanks,

I did try to write out the nested if using some instruction from one of the devs.  I'm new to this, as I typically create reporting in Crystal Reports.  I looked over the code I wrote and found the error.

Thanks for the reply.

Gianni Ceresa

If you were using the exact same as you wrote here the brackets were the problem, the bracket must be closed before the THEN as it's part of the condition [ WHEN <condition> THEN <value> ].

But good you could do it.

Of course keep in mind that nested case-when (or even just a case-when) will have an impact on the query performance as the DB must do the job, so if the analysis is too slow look if it's not because of the case-when.