Home » SQL & PL/SQL » SQL & PL/SQL » HOWTO: Column name for a SELECT obtained from an inner SELECT.
icon5.gif  HOWTO: Column name for a SELECT obtained from an inner SELECT. [message #186954] Thu, 10 August 2006 05:18 Go to next message
elflamenquin
Messages: 2
Registered: August 2006
Junior Member
Hi guys,

I have a number of tables where they have a column called "????_UNIT". I am trying to build a dinamyc SELECT to return the value of that column.

I am trying something like:

SELECT COLUMNNAME(
SELECT COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'MY-SCHEME' AND COLUMN_NAME LIKE '%UNIT' AND TABLE_NAME = 'MY-TABLE'
)
FROM MY-SCHEME.MY-TABLE
WHERE REF_CODE = 'SOMETHING'

I am building an internal SELECT to retrieve the column name for a table, and I would like to use it as the column name for the external SELECT. The only thing I get is the column name (????_UNIT).

Is there anything similar to "COLUMNNAME" I could use? Any tips?

Many thanks for your replies.


(PS: "COLUMNNAME" is invented)
Re: HOWTO: Column name for a SELECT obtained from an inner SELECT. [message #187044 is a reply to message #186954] Thu, 10 August 2006 11:21 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You won't be able to do this in a static SQL statement. You'll need to use dynamic SQL in PL/SQL code.
Re: HOWTO: Column name for a SELECT obtained from an inner SELECT. [message #187158 is a reply to message #186954] Fri, 11 August 2006 02:43 Go to previous message
elflamenquin
Messages: 2
Registered: August 2006
Junior Member
Hi Todd,

Many thanks for your reply.

My knowledge in PL/SQL is quite limited, may you please help me with the code?

Thanks.
Previous Topic: anonymous block for procedure-ref cursor out variable
Next Topic: Update table from same table
Goto Forum:
  


Current Time: Tue Dec 06 02:19:30 CST 2016

Total time taken to generate the page: 0.05370 seconds