0

I have JPA Native queries to an Oracle database. The only way I know to limit results is using 'rownum' in Oracle, but for some reason, query parser of a jar driver I have to use does not recognize it.

Caused by: java.sql.SQLException: An exception occurred when executing the following query: "/* dynamic native SQL query */ SELECT * from SFDC_ACCOUNT A  where  SBSC_TYP  = ?   and  rownum <= ?".  Cause: Invalid column name 'rownum'.  On line 1, column 90.  [parser-2900650]
com.compositesw.cdms.services.parser.ParserException: Invalid column name 'rownum'.  On line 1, column 90.  [parser-2900650]

How can I get rid of that?

Luís Palma
  • 249
  • 1
  • 7
  • 15
  • It's suspicious that the error has the name in lower case. Does it make any difference if you use upper case `ROWNUM`? (I know it doesn't matter to Oracle, but that error isn't coming from the Oracle parser.) You could also try `row_number()` in a subquery. – Alex Poole Nov 03 '18 at 00:33
  • Version of Oracle? 12c and higher supports fetch first X rows only syntax – thatjeffsmith Nov 03 '18 at 02:35
  • Possible duplicate of [How to resolve Oracle's 'rownum' pseudocolumn with JPA Criteria API?](https://stackoverflow.com/questions/20911763/how-to-resolve-oracles-rownum-pseudocolumn-with-jpa-criteria-api) – eaolson Nov 03 '18 at 03:27
  • Please post a [mcve] and post the full exception stacktrace – Mark Rotteveel Nov 03 '18 at 14:13

1 Answers1

0

ANSI Standard would be something like the following

SELECT *
FROM (
SELECT
T.*,
ROW_NUMBER() OVER (PARTITION BY T.COLUMN ORDER BY T.COLUMN) ROWNUM_REPLACE
FROM TABLE T
)
WHERE
1=1
AND ROWNUM_REPLACE < 100

or you could also use the following:

SELECT * FROM TABLE T
ORDER BY T.COLUMN
OFFSET 0 ROWS
FETCH NEXT 100 ROWS ONLY;
that_roy
  • 59
  • 9