Column name for a SELECT obtained from an inner SELECT.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Column name for a SELECT obtained from an inner SELECT.

  1. #1
    Join Date
    Aug 2006
    Posts
    2

    Question HOWTO: Column name for a SELECT obtained from an inner SELECT.

    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)

  2. #2
    Join Date
    Mar 2006
    Posts
    74
    erm. make it a view, then it will show up in user_tab_columns

  3. #3
    Join Date
    Mar 2006
    Posts
    74
    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"

  4. #4
    Join Date
    Aug 2006
    Posts
    2
    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.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  6. #6
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width