Column name for a SELECT obtained from an inner SELECT.

[elflamenquin]

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)

[cjard]

erm. make it a view, then it will show up in user_tab_columns

[cjard]

ultimately if you have a number of tables where the column name changes.. then the design is wrong.. those should be rows, with a column called "type_of_unit"

[elflamenquin]

Hi cjard,

Thanks for your reply.

I know, the DB desing is a mess. This is how it was when I came. I can not change the DB desing, several procedures are written based on the actual desing.

Somebody told me to write a PL/SQL, may you help me with that code?

Thanks.

[tamilselvan]

I can not change the DB desing, several procedures are written based on the actual desing.

Another way the client never terminates the client support.

Tamil

[WilliamR]

If this is really the path you want to go down, the code is all going to have to be dynamic. You can't do dynamic SQL within static SQL.

Why can't you just write regular static code using the actual table and column names, though?