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

Question

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

Rohit Trehan · Answer

Thanks for all the help !

Gianni Ceresa · Answer

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.

Robert Angel · Answer

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