This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Oct 3, 2006 6:23 AM by 27876 RSS

Can any one retrive Nth column from a table

537176 Newbie
Currently Being Moderated
hi 2 all
I am using oracle9i database and i want 2 retrive data from a specific cloumn knowing only column no (ie 1st col, 2nd col) througn simple sql statement ( not using pl/sql)
Thanks 2 all
himashu
  • 1. Re: Can any one retrive Nth column from a table
    RadhakrishnaSarma Journeyer
    Currently Being Moderated
    There is something called COLS table or USER_TAB_COLUMNS.

    You can have column_id from that.
    SQL>select column_name from cols
      2  where column_id = '2'
      3  and table_name = 'EMP'
      4  /
    
    COLUMN_NAME
    ------------------------------
    ENAME
    
    SQL>
    Then you may have to use Dynamic SQL to get the data of this column.

    Cheers
    Sarma.
  • 2. Re: Can any one retrive Nth column from a table
    537176 Newbie
    Currently Being Moderated
    hi Sarma
    thks 4 reply
    I have allready know that but how can retrive containt or column in a single statement
    Ur query can give only name of column.
    thanking u
    himanshu
  • 3. Re: Can any one retrive Nth column from a table
    BluShadow Guru Moderator
    Currently Being Moderated
    You're asking to do something dynamic without using dynamic SQL which would have to be done through PL/SQL. In short the answer is No you cannot do this solely through SQL.
  • 4. Re: Can any one retrive Nth column from a table
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    with sqlplus, I could imagine :
    SQL> select col&n from ( select empno col1,ename col2,job col3,mgr col4,hiredate col5,sal col6,comm col7,deptno col8 from emp);
    Enter value for n: 2
    old   1: select col&n from ( select empno col1,ename col2,job col3,mgr col4,hiredate col5,sal col6,comm col7,deptno col8 from emp)
    new   1: select col2 from ( select empno col1,ename col2,job col3,mgr col4,hiredate col5,sal col6,comm col7,deptno col8 from emp)
    
    COL2
    ----------
    SMITH
    ALLEN
    WARD
    JONES
    MARTIN
    BLAKE
    CLARK
    SCOTT
    KING
    TURNER
    ADAMS
    JAMES
    FORD
    MILLER
    
    14 rows selected.
    otherwise, you could also you a pipeline function to transform your record into a single column, casted to varchar2.
  • 5. Re: Can any one retrive Nth column from a table
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    also possible in sql
    select decode(:n,
      1,to_char(empno),
      2,ename,
      3,job,
      4,to_char(mgr),
      5,to_char(hiredate),
      6,to_char(sal),
      7,to_char(comm),
      8,to_char(deptno))
    from emp;
    
    DECODE(:N,1,TO_CHAR(EMPNO),2,ENAME,3,JOB
    ----------------------------------------
    800
    1600
    1250
    2975
    1250
    2850
    2450
    3000
    5000
    1500
    1100
    950
    3000
    1300
    
    14 rows selected.
  • 6. Re: Can any one retrive Nth column from a table
    BluShadow Guru Moderator
    Currently Being Moderated
    But OP's requirement is only knowning column position, not the column names which you'd have to know for that.
  • 7. Re: Can any one retrive Nth column from a table
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    is this a business requirement or a lazy programmer wish?
    also he does not want plsql...
  • 8. Re: Can any one retrive Nth column from a table
    BluShadow Guru Moderator
    Currently Being Moderated
    is this a business requirement or a lazy programmer
    wish?
    also he does not want plsql...
    I'll bet it's that he wants a nice generic solution to something i.e. "lazy programmer". ;)

    He should try:

    SELECT dbms_quantum.multi_world('myrequiredcolumns')
    FROM mytable

    Apparently that will give the correct columns in the correct world and all the other worlds will disappear from existence.

    ;)
  • 9. Re: Can any one retrive Nth column from a table
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    so you mean without PL/SQL and without knowing the names of the columns, at any cost of performance and design?

    well, you could try this :
    SQL> with t as (select 2 n from dual union select 6 from dual)
      2  select n,
      3  regexp_replace(regexp_substr(column_value,'<[^>]*>[^<]*</[^>]*>',1,n),'<.*>(.*)</.*>','\1') colN
      4  from xmltable('for $i in ora:view("emp") return $i'),t;
             N COLN
    ---------- --------------------
             2 SMITH
             2 ALLEN
             2 WARD
             2 JONES
             2 MARTIN
             2 BLAKE
             2 CLARK
             2 SCOTT
             2 KING
             2 TURNER
             2 ADAMS
             2 JAMES
             2 FORD
             2 MILLER
    
             N COLN
    ---------- --------------------
             6 800
             6 1600
             6 1250
             6 2975
             6 1250
             6 2850
             6 2450
             6 3000
             6 10
             6 1500
             6 1100
             6 950
             6 3000
             6 1300
    
    28 rows selected.
  • 10. Re: Can any one retrive Nth column from a table
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    of course this will not work in Oracle7, not even in Oracle 10gR1 !
  • 11. Re: Can any one retrive Nth column from a table
    BluShadow Guru Moderator
    Currently Being Moderated
    Well it works in 10gR2, but boy, performace aint its strong point.
  • 12. Re: Can any one retrive Nth column from a table
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    at least the execution plans looks good !
    SQL> set timi on autot on
    SQL> select regexp_replace(regexp_substr(column_value,'<[^>]*>[^<]*</[^>]*>',1,2),'<.*>(.*)</.*>','\1') col2 
    from xmltable('for $i in ora:view("emp") return $i');
    COL2
    ----------
    SMITH
    ALLEN
    WARD
    JONES
    MARTIN
    BLAKE
    CLARK
    SCOTT
    KING
    TURNER
    ADAMS
    JAMES
    FORD
    MILLER
    
    14 rows selected.
    
    Elapsed: 00:00:00.04
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3956160932
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |    15 |   525 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| EMP  |    15 |   525 |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              4  consistent gets
              0  physical reads
              0  redo size
            584  bytes sent via SQL*Net to client
            380  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             14  rows processed
  • 13. Re: Can any one retrive Nth column from a table
    BluShadow Guru Moderator
    Currently Being Moderated
    Well I dunno, it looks like this on mine...
    -----------------------------------------------------------------------------
    | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |      |    28 |  1120 |    10  (20)| 00:00:01 |
    |   1 |  MERGE JOIN CARTESIAN|      |    28 |  1120 |    10  (20)| 00:00:01 |
    |   2 |   VIEW               |      |     2 |     6 |     6  (34)| 00:00:01 |
    |   3 |    SORT UNIQUE       |      |     2 |       |     6  (67)| 00:00:01 |
    |   4 |     UNION-ALL        |      |       |       |            |          |
    |   5 |      FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
    |   6 |      FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
    |   7 |   BUFFER SORT        |      |    14 |   518 |    10  (20)| 00:00:01 |
    |   8 |    TABLE ACCESS FULL | EMP  |    14 |   518 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------
  • 14. Re: Can any one retrive Nth column from a table
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    sure, this is due to my dummy table which select two columns, check the sql statements, in the second one, I suppressed the with t as (select ... from dual) to get only the relevant plan :-)
1 2 Previous Next