Re: For SQL-Freaks: Column-Renaming in Oracle-SQL (version 7)

berndl_at_urolph.uni-passau.de (Klaus Berndl) wrote:
>I have the following SQL-problem:
>
>I only know the tablename and the number of columns of a certain table
>(e.g. 'supplies' with col-number = 3 ). I only want to fetch the values of
>the columns one and three (its only an example, i need a general solution!) of 'supplies'.
>To my knowledge, the following standart-sql-instruction (renaming of column-
>names) is correct:
>
>SELECT R.c0, R.c2
>FROM supplies R(c0, c1, c2)
>WHERE R.c1 > 12.00;
>
>The problem is now, ORACLE 'SqlPlus' (Version 7) doesn`t agree with me relating
>to correctness of the statement above!
>
>
>Many thanks for relevant informations either for correction the statement above
>or completely different solutions for my problem!
>
>Klaus Berndl
>

You need to query user_tab_columns to get the column names, then construct the query from the results using dynamic sql. You'll need to use pl/sql or better (OraPerl, ProC) to do this.

One point, Although it's debatable, I'm not sure that strictly speaking column order is a meaningful concept in a rdbms.

-- 
************************************************************
* Kevin Woolley *       WoolleySoft.  Producers of         *
*               * Landscape Explorer & Landscape Animator  *
*               *  3D Landscape, Satellite image and Map   * 
*               *          Visualization software          *
************************************************************
*  Author-Member: Association of Shareware Professionals   *
* email: 100332.2104_at_compuserve.com or kjw1_at_stirling.ac.uk *   
* www:   http://www.stir.ac.uk/~kjw1r/home.html            *
************************************************************