Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Nested Case Statement Help

Received Response
822
Views
4
Comments
Mik A.M.
Mik A.M. Rank 2 - Community Beginner

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.

Answers

  • 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
    Bose Pandian Rank 3 - Community Apprentice

    Hi Mik A.M,

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

  • Mik A.M.
    Mik A.M. Rank 2 - Community Beginner

    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.

  • 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.