Skip to Main Content
  • Questions

  • Simple Query using a function for a column name

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Joshua.

Asked: January 03, 2011 - 10:22 pm UTC

Last updated: January 05, 2011 - 7:29 am UTC

Version: 10.2.1

Viewed 1000+ times

You Asked

I have a table named "MyTable" it has columns c1,c2,c3,c4,c5.
I have a function called "MyFunction" that returns one and only one column name from "MyTable" such that 
SELECT MyFunction(1,0) FROM DUAL;
might return "c4".

Now I want to use the return from the function as a column name in a select statement. Such that 
SELECT c1,c2,"result of MyFunction as a column name" FROM MyTable
would result in an dataset equivalent to 
SELECT c1,c2,c4 FROM MyTable
in SQL Developer results pane.

and Tom said...

You cannot - the name of the column is fixed at PARSE time (it has to be - there would be no way to compile it otherwise). You would be asking to set things AFTER the parse is complete. It doesn't work that way.


You would have to run the function - get the result - and then build a query that incorporated the result.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

suggestion

Alistair Wall, January 04, 2011 - 8:55 am UTC

SELECT c1,c2,case MyFunction when 1 then c3 when 2 then c4 end mycol FROM MyTable
Tom Kyte
January 04, 2011 - 9:08 am UTC

Now I want to use the return from the function as a column name in a select statemen

how would your suggestion use the output of myfunction as a COLUMN NAME?

I see know this question could be interpreted in more than one way.

The way I read it was "they wanted to use the output of myfunction as a column name - the name of a column selected in the query - like an alias"

The other way to read it was "they wanted to use the output of myfunction to become an attributed selected from the table"

In either case the simple answer is "no"

In the latter case - your technique would work fine ASSUMING the columns C3, C4 had compatible datatypes. I would want to still select myfunction in addition to the CASE so I could tell what I was actually seeing.

As an Alias

Joshua Briskin, January 04, 2011 - 10:42 pm UTC

Actually, the question states explicitly I want to use the output of a function as the name of a column in a query; "result of MyFunction as a column name" - So Tom is correct in his first read.

But still to the first question... How can the result be accomplished? Essentially, I want to select some know columns [c1,c2] from a table plus one of several columns that is only applicable based upon the result of the function. The function essentially returns the name of the column that is currently applicable. Next week, the applicable column might change; but the function can always and exclusively determine the name of the column currently applicable.
Tom Kyte
January 05, 2011 - 7:29 am UTC


how to accomplish that - using CASE (or decode). The caveats are

o the NAME of that selected attribute will be constant (you will decide that name when you write the query).

o the datatypes of the underlying columns had better be compatible

Those caveats are immutable.


The query would look like:

select a, b, 
       case myfunction when 1 then c1 when 2 then c2 ... end DATA,
       myfunction NAME_OF_DATA
  from t


It has to be that way - one of the reasons is that the choice of a column selected from a table can and will change query plans. A query plan is developed before the query is executed. If the query must partially execute to figure out what columns it would be selecting..... We have a chicken and egg problem. The other problem is that IN GENERAL the result from myfunction could change in the middle of a query - meaning the plan would have to change midstream - that is just not possible.


The alternative would be to use dynamic sql.

You would

a) execute myfunction
b) use the output of that to construct a query referencing the column explicitly



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.