CODEWITHSUNDEEP
sqloracle-database
TaroYuki
TaroYuki

Reputation: 147

How to use subquery result as the column name of another query

I want to use the result from subquery as the column name of another query since the data changes column all the time and the subquery will decide which column the current forcast data stored. My example:

select item,
      item_type
      ...
      forcast_0 * 0.9 as finalforcast
      forcast_0 * 0.8 as newforcast
from sales_data.

but the forcast_0 column is the result (fore_column_name) of the subquery, the result may change to forcast_1 or forcast2

select 
     fore_column_name 
     from forecast_history 
     where ...

Also, the forcast column will be used multiple times in the first query. how could I implement this?

Upvotes: 2

Views: 4995

Answers (1)

amcdermott
amcdermott

Reputation: 1585

Use your sub query as an inline table. Something like....

select item,
       item_type,
       ..
       decode(fore_column_name, 'foo', 1, 2) * 0.9 as finalforcast,
       decode(fore_column_name, 'foo', 1, 2) * 0.8 as newforcast
  from sales_data,
       (
          select fore_column_name
            from forecast_history 
           where ...
       ) inlineTable

I'm assuming here that the value from the sub-query will be the same for each row - so a quick cross-join will suffice. If the value will vary depending on the values in each row of the sales_data table, then some other type of join would be more appropriate.

Quick link to decode - in case you aren't familiar with it.

Upvotes: 2

Related Questions