apps-infra

    Forum Stats

  • 3,874,066 Users
  • 2,266,672 Discussions
  • 7,911,718 Comments

Discussions

Using decode function

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
Tagged:

Answers

  • Goran Ocko
    Goran Ocko Member Posts: 554
    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
    728768 Member Posts: 255
    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
    710389 Member Posts: 193
    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
    728768 Member Posts: 255
    edited Oct 30, 2009 10:03AM
    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
    710389 Member Posts: 193
    duplicate that column and change the count by sum...isn't that what you need?

    Kr,
    A
  • Goran Ocko
    Goran Ocko Member Posts: 554
    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
    728768 Member Posts: 255
    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
    Goran Ocko Member Posts: 554
    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
    728768 Member Posts: 255
    thanks to everyone for help. Problem solved.

    regards
  • Goran Ocko
    Goran Ocko Member Posts: 554
    edited Oct 30, 2009 3:28PM
    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
This discussion has been closed.
apps-infra