Creating an OBIEE analysis that uses a filter to group items in a column
I'm need to group several items in an analysis that I'm building in OBIEE 11g
In my subject area I have a table called result_code. The codes are are numeric starting with 100, 200, 300, 400, etc. all the way up to 900. I want to put codes 100-400 into a group called "Success" and put codes 500-800 in a group called "Fail", leaving code 900 for a group call "Unknown"
I'm trying to do this in an analysis filter by selecting 'SQL expression' under 'Add More Options and entering the case statement below.
SELECT case when result_code = '100' then 'Success'
when result_code = '200' then 'Success'
when result_code = '300' then 'Success'
when result_code = '400' then 'Success'
when result_code = '500' then 'Fail'
when result_code = '600' then 'Fail'
when result_code = '700' then 'Fail'
when result_code = '800' then 'Fail'
else 'Unknown' end case
FROM "Results"
I get a syntax error when I try to run this. I'm not sure if entering a SQL expression is the right way to approach this. What is the best way to group items in a filter? If a SQL expression is the right way to go about this what should the syntax look like?