>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).
