nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Help with a case function on two columns

fabryddorf-Oracle

Hi Experts,

On the below report, I want to sum up all market codes for which the description contains the word Retail.

I thought it is an easy one, but tried several things and always getting a syntax error.

I tried following:

CASE WHEN "Marketing"."Market Description" like '%Retail%' THEN "Marketing"."Market Code"='RETAIL' ELSE Marketing"."Market Code" END

CASE WHEN "Marketing"."Market Code" or "Marketing"."Market Description" like '%Retail%' THEN 'RETAIL' ELSE "Marketing"."Market Code" END

I am not an expert, so pretty sure I am doing some mistakes in thinking there.

Find more posts tagged with

Accepted answers

All comments

[Deleted User]

Assuming that you want to create a new "bin" column which aggregates all "retail" members into one:

CASE WHEN "Marketing"."Market Description" like '%Retail%' THEN 'RETAIL' ELSE 'SOMEHTING ELSE' END

CASE WHEN "Marketing"."Market Description" like '%Retail%' THEN 'RETAIL' ELSE "Marketing"."Market Description" END

fabryddorf-Oracle

Hi Christian,

Maybe i was not clear enough. I am using the description to easily identify specific market codes, but basically all i want to group are the market codes based on description.

When description like %Retail%' summarize the market codes and call them RETAIL.

it should summarize (BEN+ECM+MAR+REG) into a market code called RETAIL. I do not need to show the description in the report.

Hope it makes sense

I d

fabryddorf-Oracle

Don't know if it is possible at all

[Deleted User]

Just a hint: As an employee, you have to have your name in your user profile. You can't have a non-sensical user handle.

And the solution is extremely simple if you look at what I wrote and think about it:

CASE WHEN "Marketing"."Market Description" like '%Retail%' THEN 'RETAIL' ELSE "Marketing"."Market Description" END

Make that:

CASE WHEN "Marketing"."Market Description" like '%Retail%' THEN 'RETAIL' ELSE "Marketing"."Market Code" END

Gianni Ceresa

You can't change the value of a column coming from somewhere else (that's something you should do in a database), what you can do is to have a column with a formula that perform some logic and display one thing or another based on your needs.

CASE WHEN in OBIEE is fairly similar in syntax as to any language having conditions:

CASE WHEN <condition> THEN <value1> ELSE <value2> END

Christian gave you the right condition, he also gave you value1, so instead of using a hardcoded "value2", just use the reference to the column you need to get the value from.

But you can't assign a value with a = in "value 1" or "value 2".

Gianni Ceresa

https://community.oracle.com/tech/apps-infra/discussion/comment/16816869#Comment_16816869

About 10 seconds: 5 to copy/paste what Christian gave you, 5 to replace one of the values with the column reference you need.

fabryddorf-Oracle

Thank you Gianni for the suggestion.

I did not appreciated the cinical ton from Christian between colleagues however. I already said, I am not an expert and prefer rather no answer to some arrogant answers.

Below is what Christian suggestion returns. It transforms the description into market codes and group the market codes.

CASE WHEN "Marketing"."Market Description" like '%Retail%' THEN 'RETAIL' ELSE "Marketing"."Market Code" END

In the pivot table, I then excluded the market.code column, and now I have the revenue summarized by RETAIL market code, which is what I was looking for.

Thanks both