Categories
Nested Case Statement Help
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?
0 -
Hi Mik A.M,
Try with the same syntax you wrote. It will work both in RPD and Answer Side.
0 -
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.
0 -
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.
0
