How to create a logical column which is an "OR" of 3 existing columns in the same table in the front

Rohit Trehan · Sep 27, 2017 9:58PM

Hi

I am interested to implement the following scenario in OBIEE front-end ot RPD.

My goal is to create a Pseudo/Logical column in the table which can unpivot or unflatten the 3 columns Name 1, Name 2 and Name 3.

I can create this column by creating an Opaque view which the SQL:

select distinct Name1 as Pseudo_Col

from Table 1

Union

select distinct Name2 as Pseudo_Col

from Table 1

Union

select distinct Name2 as Pseudo_Col

from Table 1

But i don't want to go that route.

How can i do that in the same table.

Thanks

Rohit

Table 1
ID	Name 1	Name 2	Name 3	Pseudo/Logical Col = Col B UNION Col C union Col D
1243	bazooka	whiskey	Rum	bazooka
				whiskey
				Rum

Martin van Donselaar · Sep 28, 2017 3:54AM

What is your exact requirement? Just transposing these columns to rows by itself makes no sense at all

Robert Angel · Sep 28, 2017 4:37AM

Sounds like a pivot table - but - a fuller description of what you are trying to achieve would help?

Gianni Ceresa · Sep 28, 2017 4:42AM

OBIEE is great but still has no options for miracles ....

Even by hand in SQL you don't have a magical function doing that other than select each column and union all the result one after the other, so in OBIEE you also don't have magical solutions to that.

You can do the same UNION in the analysis itself if it's for just one or few analysis.

More in general this kind of things need to be managed on the data side, when creating a clean dimension in your ETL.

Rohit Trehan · Sep 28, 2017 8:44AM

Thanks for all the help !

Oracle Analytics Forum

Categories

How to create a logical column which is an "OR" of 3 existing columns in the same table in the front

Answers