Re: selecting all columns but one?

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 26 Jun 2002 13:05:40 -0700
Message-ID: <afd6qk02uri@drn.newsguy.com>

In article <9fe1f2ad.0206261157.1ba820dd_at_posting.google.com>, laredotornado_at_zipmail.com says...
>
>Hello,
> (Running Oracle 8.1.7). I'm looking for a way in SQL to select all
>the columns except a particular one without explicitly listing all the
>columns I want. So given the query below ...
>
> SELECT Q2.* FROM
> (SELECT Q1.*, ROWNUM RID FROM (p_query) Q1) Q2
> WHERE RID BETWEEN p_low AND p_high
>

that would be better as:

select q2.*
  from ( select q1.*, rownum rid

           from ( p_query ) q1
          where rownum <= p_high ) q2

 where rid >= p_low

There are effeciencies to be gained that way (we can abort the query with a STOP when you use the rownum <= p_high as far "in" as possible like that)

But no, there is no way to select all but "one" column from a query where you've asked for all of the columns. The person fetching from this query would have to know "leave that last one alone"  

>how do I modify it so that the only columns returned are those in "Q2"
>and not the column named "RID"?
>
>If a solution to this problem exists, it would be most helpful if it
>were in SQL (and not SQL*Plus) as I am forming this query through
>JDBC.
>
>Thanks in advance, Dave A.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Wed Jun 26 2002 - 15:05:40 CDT