Re: SELECT rows range from a view?

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 1 Jun 2002 06:18:43 -0700
Message-ID: <adahjj02619@drn.newsguy.com>

In article <ad9ahf$ujsjq$1_at_ID-114658.news.dfncis.de>, "Giovanni says...
>
>Hi all,
>
>How can one retrieve only rows X to Y from a *VIEW*?
>
>I know it is possible using the ROWID and ROWNUM
>but what from a complex view?
>
>SELECT *
> FROM tableX
> WHERE rowid in (
> SELECT rowid FROM tableX
> WHERE rownum <= 7
> MINUS
> SELECT rowid FROM tableX
> WHERE rownum < 5);
>
>but if TableX is a VIEW I get the error:
>cannot select ROWID from a join view without a key-preserved
>table
>
>Thanks in advance,
>Giovanni
>
>

Just

select *
  from ( select a.*, rownum rnum

           from ( YOUR_QUERY_GOES_HERE ) A
          where rownum <= :MAX_ROW )

 where rnum >= :MIN_ROW;

Thats all -- no rowids, no minus.

--
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 Sat Jun 01 2002 - 08:18:43 CDT