EXASolution's ROWNUM is not compatible with Oracle's ROWNUM!
While ROWNUM in Oracle can be used to limit output data of almost arbitrary statements, EXASolution implements ROWNUM while sticking to the overall SQL semantics: Anything you put into the WHERE clause of a statement filtersinput data. To avoid confusion and seemingly wrong results, we only allow ROWNUM in situations where the result is in line with Oracle's semantic.
Example to clarify:
Assumed there's a table "customer" containing a large number of "Schmitts".
select * from customer where c_name like'Schmitt%';
-- works, but too many rowsselect * from customer whererownum < 11;
-- works, only ten rows, but no"Schmitt's"select *
where c_name like'Schmitt%'andrownum < 11;
-- Error, ROWNUM can't be combined with other conditions inwhere clause
In the last statement, Oracle would first filter for all the Schmitts and only output the first 10 matches. Using strict SQL semantics, the filters on c_name and ROWNUM would be independent, meaning that only Schmitts appearing in the first 10 rows of the table get returned. As this is probably not what you expect (coming from Oracle), we prevent this statement:ROWNUM has to be the only one condition in the where clause
The follwoing SQL depicts a Workaround / Solution:
-- usea subselect
from (select *
where c_name like'Schmitt%')
whererownum < 11;
-- works, ten rowswith"Schmitt's"-- useLIMITinsteadofROWNUMselect *
where c_name like'Schmitt%'LIMIT 10;
Additionally, there are some statements which generally don't allow the usage of ROWNUM
select c_name with invalid primarykey (c_custkey)
from customer whererownum < 11;
-- Error, ROWNUM cannot be used in combination with this statement