0

I'm tryint to select a subset of records, 5000 through 10000 from a join. I've gotten queries like this to work in the past, but they were slightly less complex. Here is the query I'm trying to use and if I remove the rownum/rnum references (and therefore the outer select) I receive all my records as expected so I know that logic is good. 

SELECT      * 
    FROM    ( 
            SELECT  unique cl.riid_, 
                    rownum as rnum 
            FROM    <table 1> cl, <table 3> mil 
            WHERE   cl.opt = 0 AND 
                    (cl.st_ != 'QT' OR cl.st_ IS NULL) AND 
                    cl.hh = 0 AND 
                    cl._ID_ = mil._ID_ AND 
                    mil.TYPE in (0, 1, 2, 3, 4) AND 
                    EXISTS 
                        ( SELECT    'x' 
                            FROM    <table 2> sub 
                            WHERE   cl.ea_ = lower(sub.ea_) AND 
                                    sub.status = 0 AND 
                                    lower(sub.subscription) = 'partner' 
                        ) AND 
                    rownum <= 10000 
            ) 
    where   rnum > 5000

So when I run this query I receive this message from our system (this is not an Oracle cli interface, but rather a web layer that exists over the top of it so please bare with the error msg if it's out of the ordinary)

'Error: The resource selected for viewing is invalid. You may need to re-create or fix the object before viewing its data.'

The resource would be the results of the query.

So does anyone have an idea of whats going on or a better way to do this?

Thanks!

dscl
  • 1,616
  • 7
  • 28
  • 48
  • 5
    There's no reason for it not to work but without an ORDER BY the order of the result set is indeterminate, so why not just pick out the first 5000 rows. There is nothing in the query that will put any particular row in the 'second' 5000 as opposed to the 'first' 5000. – Gary Myers Feb 01 '11 at 05:03
  • Have you tried running the SQL in SQL Plus rather than your OO tool to see if it is OK? – Tony Andrews Feb 01 '11 at 12:24
  • @Gary I forgot the ORDER BY whoops! As for the second part what I need to actually do is write 4 queries. One to return the first 5000, then the next 5000 and so on. Thus my need to return that second 5K. The first query works great it's just these 2nd, 3rd, 4th set queries which weren't working. – dscl Feb 01 '11 at 14:07
  • @Tony unfortunately I don't have the ability to do so. – dscl Feb 01 '11 at 14:07
  • The error message doesn't look like an Oracle message. What tool are you using to run the query? – Jeffrey Kemp Dec 07 '11 at 07:08

1 Answers1

2

I believe you're looking for something like this in Oracle:

select * 
  from ( select a.*, rownum rnum
           from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
          where rownum <= MAX_ROWS )
 where rnum >= MIN_ROWS
/

Good discussion from Ask Tom is here

tbone
  • 15,107
  • 3
  • 33
  • 40
  • thanks for the response, but unless I am missing something isn't that what I'm already doing in the example? – dscl Feb 01 '11 at 14:13
  • not quite from first glance, you should have something like: select * from (select a.riid, rownum rnum from (select cl.riid, ... where ... order by ...) a where rownum <= MAX_ROWS) where rnum >= MIN_ROWS; – tbone Feb 01 '11 at 16:48
  • dscl - the subtle difference that I believe tbone is trying to point out is that rownum is assigned before the rows are ordered by the ORDER BY. Therefore, you need one more level of nesting. You will have the original query in the correct order that you want (first level; example output: rows 1-100,000). Then you select from that and assign rownum to an aliased column and limit the query to the upper limit that you need (level 2; example output: rows 1-10,000). Then you select from that and set your bottom limit (level 3; example output: rows 5,000-10,000). – Craig Feb 02 '11 at 20:46