Problems with ROWNUM

I have a SQL query that returns the top 15 customers. It works fine in Oracle SQL Developer, but when I use the query to create a data set, and generate the report in BIRT, only two rows are returned (2 and 6). If I remove the "where rownum <= 15" filter, all the rows return as expected. I've also tried adding the filter directly in BIRT, rather than coding it directly into the query, but it has the same issue. Has anyone seen something like this happen before?

Find more posts tagged with

Comments

Hans_vd

Hi Talva,

This is very strange behaviour.
Are you sure you have no additional filters in your data set or in your table?

talva

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="110104" data-time="1349296526" data-date="03 October 2012 - 01:35 PM">
Hi Talva, 
 
This is very strange behaviour. 
Are you sure you have no additional filters in your data set or in your table? </blockquote>
 
Strange indeed. I was eventually able to get it to work, although I'm still not sure why it wouldn't to begin with, but it did have something to do with one of my filters. The query was selecting from several subqueries, e.g. 
 
Select rownum, id, z, a, b, c from (select id, z, sum(a) a,sum(B) b,sum(c) c group by id, z order by z from (select id, z, a, b, c from table)) where id = 98 and rownum <= 15 
 
Something like that, anyway, I was bringing that ID value up to the top level and then trying to filter on it, and it was not working. I eventually got it to work by adding a replace script, and replacing the id at the sub level, e.g. 
 
Select rownum, id, a, b, c from (select id, sum(a) a,sum(B) b,sum(c) c group by a from (select id, a, b, c from table where id = 'SOMENUMBER')) where rownum <= 15 
 
and putting this into the beforeOpen script this.queryText = this.queryText.replace("SOMENUMBER", params["GenerationDate"]) 
 
I am under a bit of a time crunch so I didn't have time to fully investigate what was going on, but it works now!

Hans_vd

I see, not so strange then.

If you have the query with the where rownun <= 15 and a filter on it, the query will be executed and then the ID filter will get a subset from the records in the dataset, of which only some may have the ID equal to the ID in the filter.
If you change the querytext, the query will be executed for the selected ID only.