Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Location
    HK
    Posts
    14

    Unanswered: Trouble with SELECT statement

    Hi,

    How can I SELECT only ONE TABLE_NAME & COLUMN_NAME from the USER_TAB_COLUMNS?

    Example:

    If I have the data below:

    TABLE_NAME COLUMN_NAME
    -------------------- ----------------
    COUNTRIES COUNTRY_ID
    DEPARTMENTS DEPARTMENT_ID
    DEPARTMENTS DEPARTMENT_NAME
    EMPLOYEES EMPLOYEE_ID
    EMPLOYEES LAST_NAME
    EMPLOYEES EMAIL
    EMPLOYEES HIRE_DATE
    EMPLOYEES JOB_ID

    I just want to SELECT only ONE COLUMN_NAME from ONE TABLE_NAME, from the data above:

    Desired result:

    COUNTRIES COUNTRY_ID
    DEPARTMENTS DEPARTMENT_ID
    EMPLOYEES EMPLOYEE_ID

    I do not need to duplicate the TABLE_NAME.

    I do NOT want this resut:

    COUNTRIES COUNTRY_ID
    DEPARTMENTS DEPARTMENT_ID
    EMPLOYEES EMPLOYEE_ID
    EMPLOYEES LAST_NAME <--- (duplicate TABLE_NAME)

    Thanks
    Ricky

  2. #2
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200
    If u need the values from the table user_tab_columns then here u go :
    SQL> select table_name,column_name,column_id from user_tab_columns where column_id=1;

    But for other tables this changes,for a general method i will try to get back with something .

    mickykt

  3. #3
    Join Date
    Jul 2003
    Posts
    1

    Re: Trouble with SELECT statement

    try

    SELECT TABLE_NAME,MIN(COLUMN_NAME)
    FROM user_tab_columns
    GROUP BY TABLE_NAME;

    or

    SELECT TABLE_NAME,COLUMN_NAME FROM
    (SELECT TABLE_NAME,COLUMN_NAME,ROWNUM rn
    FROM user_tab_columns) b
    WHERE b.Rn IN (SELECT MIN(ROWNUM)
    FROM user_tab_columns
    GROUP BY TABLE_NAME);
    Last edited by gwiegman; 07-16-03 at 18:47.

  4. #4
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Appropriate query

    select table_name, column_name from user_tab_columns where column_name like '%_id';

    Hope that solves the purpose.

    You could also use
    select table_name, column_name from user_tab_columns where column_name in ('country_id','department_id','employee_id');

  5. #5
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    user_tab_columns

    The user_tab_columns come in handy while designing the data dictionary.
    If there you are using the query for, do let me know for any issues or help.

    Thanx and Regards
    Aruneesh

  6. #6
    Join Date
    Jun 2003
    Location
    HK
    Posts
    14
    Hi guys, thanks for the help.

    I did pickup a few things from your suggestion.

    The use of COLUMN_ID from mickykt.
    The 2nd SELECT statement suggested by gwiegman (but I used COLUMN_ID instead of ROWNUM)
    Aruneesh, if only I had to work with one table, your suggestion would have been the simplest. Unfortunately it's 2328 tables so quite a nightmare to hardcode column names. :-(

    Thank you all! :-)
    Ricky

Posting Permissions

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