RE: ROWNUM is driving me nuts - queries suggested produced no results

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Wed, 09 Jul 2003 09:01:18 -0700
Message-ID: <F001.005C4152.20030709084930@fatcity.com>

>I definitely dont fully understand ROWNUM yet,
>and you guys so far provided more info than a
>couple of books
>by Oracle, that I have here. For a second I thought
>I'm
>beginning to get it, but the queries suggested
>produced no results...
>
>
>SQL> SELECT ROWNUM, GENDER
> 2 FROM (SELECT ROWNUM, GENDER
> 3 FROM EMP2
> 4 WHERE ROWNUM <= 20)
> 5 WHERE ROWNUM > 10;
>
>no rows selected

All right, what does the INNER query return? The 20 first rows it gathers from Oracle. This is an inline view, so you can consider this to be a new table we are returning rows from at the OUTER level.
The snag with ROWNUM is that it is a reserved word. So when Oracle parses the query, it considers that you want the rows (for the OUTER query) starting from the 11th - by virtue of the 'counting-on-the-fly' nature of ROWNUM, you never get any row since the first one you could get would be numbered 1, as has already been explained to you. What do you really want, at the OUTER level? You want the INNER rownum to be greater than 10, not the OUTER, current rownum. What you need to understand is that once you jump from an inner to an outer queries, columns, whether they are true ones, computed ones or pseudo ones, from the inner query take a life of their own. The trick is to rename the (pseudo) column of the inner query (I return both ROWNUMs so that you can check how they differ) :

SQL> SELECT ROWNUM OUTER_ROWNUM, INNER_ROWNUM, GENDER   2 FROM (SELECT ROWNUM INNER_ROWNUM, GENDER

  3           FROM   EMP2
  4           WHERE  ROWNUM <= 20)

  5 WHERE INNER_ROWNUM > 10; Once you have understood this, the rest will come naturally ... I still believe that it's easier and faster to expand the RBS tablespace ...

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Jul 09 2003 - 11:01:18 CDT