Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

Nested Case Statement Help

Received Response
825
Views
4
Comments
Mik A.M.
Mik A.M. Rank 2 - Community Beginner
edited Aug 13, 2024 6:05PM in Oracle Analytics Cloud and Server

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

  • Gianni Ceresa
    Gianni Ceresa Mod

    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.

  • Gianni Ceresa
    Gianni Ceresa Mod

    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.