1 2 Previous Next 16 Replies Latest reply on Oct 3, 2006 1:23 PM by Kamal Kishore

    Can any one retrive Nth column from a table

    537176
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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