Oracle: get X rows from point A

Question

I have a stored procedure which gets a list of items attached to a given configuration (each item is attached to one configuration ; if it's not (i.e. the field is NULL) then we consider it attaced to the default configuration (wich has its field ISDEFAULT to 1). I would like to get the items using pagination. I remembered that the ROWNUM field starts at one, so I wrote (using 0 for the first page, and ten items per page):

SELECT *
FROM 
  ITEMS item
WHERE
  (ROWNUM - 1) >= 0 and
  (ROWNUM - 1) < (0 + 10) and
  (item.CONFIGID = 0 or 
  item.CONFIGID is null and exists (SELECT config.CONFIGID FROM CONFIGURATION config WHERE config.CONFIGID = 0 and config.ISDEFAULT = 1));

This request returned me zero results.

I changed it to:

SELECT item.NAME,
item.ITEMID,
item.CONFIGID,
ROWNUM
FROM 
  ITEMS item
WHERE
      (ROWNUM - 1) >= 0 and
      (ROWNUM - 1) < (0 + 10) and
  (item.CONFIGID = 0 or 
  item.CONFIGID is null and exists (SELECT config.CONFIGID FROM CONFIGURATION config WHERE config.CONFIGID = 0 and config.ISDEFAULT = 1));

And it worked! Adding the ROWNUM in the SELECT statement fixed the issue.

Then I tried:

SELECT *
FROM 
  ITEMS item
WHERE
  ROWNUM >= (0 + 1) and
  ROWNUM < (0 + 1 + 10) and
  (item.CONFIGID = 0 or 
  item.CONFIGID is null and exists (SELECT config.CONFIGID FROM CONFIGURATION config WHERE config.CONFIGID = 0 and config.ISDEFAULT = 1));

I wrote back the SELECT * statement and moved the - 1 to the other sides of the comparison operators (therefore they became + 1). This works as well.

Can someone explain why my first query does not work and yields zero results whereas the other two work like a charm?

Answer 1

I think there is nothing wrong with ROWNUM. Can you try to execute:

SELECT *
  FROM ITEMS item
 WHERE
  (ROWNUM - 1) >= 0 and
  (ROWNUM - 1) < (0 + 10);


SELECT item.NAME,
       item.ITEMID,
       item.CONFIGID,
       ROWNUM
  FROM ITEMS item
 WHERE
  (ROWNUM - 1) >= 0 and
  (ROWNUM - 1) < (0 + 10);

Both should work ok. Which means you have problem with second part of the filter. The following subquery

(SELECT config.CONFIGID FROM CONFIGURATION config WHERE config.CONFIGID = 0 and config.ISDEFAULT = 1));

is uncorreleated. So it whould always return same result for any record. I think you have to add brackets into condition by item.CONFIGID:

(item.CONFIGID = 0) or 
(
item.CONFIGID is null and
exists (SELECT ...)
)

or

(item.CONFIGID = 0 or     
item.CONFIGID is null) and
exists (SELECT ...)

I think that is your problem. But not sure without seeing data.