Using decode function

728768

Hi

Help needed.... i need to create column as L, R in fact table in business layer using below mentioned logic

select count(decode (type, '41',1,'51',1,'61',1))L, count(decode(type, '71',1,'31',1,'81',1))R

Waiting for the reply soon.

thanks & regards

Edited by: user11933655 on 29-Oct-2009 10:26

Goran Ocko

Similar thread/question:
3729455

You may use:
L -> case when table.column in ('41', '51', '61') then 1 else 0 end
R -> case when table.column in ('71', '31', '81') then 1 else 0 end

Assuming that table.column is dimension attribute and you joined this table dimension to a fact table (steps 2 and 3 from the link above, add physical join to dimension table in the logical fact table source). L and R are two logical columns, using COUNT or SUM as aggregation rule.

Follow steps 1-7 (1-9 for complete) from the link above.

Regards
Goran
http://108obiee.blogspot.com

728768

Hi

Thanks for the reply. I have created logical column xy in fact table using case function and applied count aggregation on it..... now i want to create one logical column as sum of xy.

waiting for the reply.

With warm regards

710389

duplicate your object and instead of aggregation rule count, you select sum.
Hope this helps, otherwise create a new logical column with the same logic and aggregation on som.

Kr,
A

728768

thanks for reply but the problem i am getting is that my xy column has created as "count(case when TYPE in ([ 71, 72, 73, 74] ) then 1 end)" and now i want to create logical column x as "sum(xy)".

Kind regards

Edited by: user11933655 on 30-Oct-2009 07:03

710389

duplicate that column and change the count by sum...isn't that what you need?

Kr,
A

Goran Ocko

What is the source for x measure?
What is the source for y measure?

Are x and y two separate measure like I provide in solution above?

Please answer this questions.

If you want to sum x and y just make new logical column and in the general tab/use existing logical columns as the source put x+y.

Regards
Goran
http://108obiee.blogspot.com

728768

Hi Goran

Sorry for the confusion.

I am able to create logical column L as "count(case when table.column in ('41', '51', '61') then 1 else 0 end)". now i want to create one new logical column as "sum (L)".

I have tried copying L column and rename it as X and used selected sum in aggregation tab.. here the formula for logical column for X is coming as sum(case when table.column in ('41', '51', '61') then 1 else 0 end).

Kind regards

Goran Ocko

Why you want to make sum(L)?

You already summarize your data with measure (count):
count(case when table.column in ('41', '51', '61') then 1 else 0 end)

It's the same as:
sum(case when table.column in ('41', '51', '61') then 1 else 0 end)

Regards
Goran
http://108obiee.blogspot.com

728768

thanks to everyone for help. Problem solved.

regards

Goran Ocko

If this thread/question is answered it would be nice of you to follow the rules of this forum and put points to people/answers that helped you.

If you are new on the forum please read:
http://forums.oracle.com/forums/ann.jspa?annID=939

Regards
Goran
http://108obiee.blogspot.com