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

Rohit Trehan

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

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

Robert Angel

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

Gianni Ceresa

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

Thanks for all the help !