LIMIT breaking @Query

Question

so...

following loooong query

@Query(value = "SELECT A, B ... FROM ADMIN.SUPER_SEARCH WHERE A = :param OR B = :param ORDER BY A desc", nativeQuery = true )

will work just fine (i replaced few fields with ... it is a looong query)

but as soon as i add LIMIT 5 to end of query, it will throw

Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

so this is the error query

@Query(value = "SELECT A, B ... FROM ADMIN.SUPER_SEARCH WHERE A = :param OR B = :param ORDER BY A desc LIMIT 5", nativeQuery = true )

WHY?

I would really need the limit here, since table is about 300k rows long..

Have a look at [this](https://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering) — Aleksej, Mar 14 '19 at 16:06
As an alternative, you can use setFirstResult (offset) and setMaxResults (limit) when creating the named query — Diego Victor de Jesus, Mar 14 '19 at 16:09
Since you are using a native query, you must be sure 'LIMIT' is the proper oracle syntax to limit queries — Diego Victor de Jesus, Mar 14 '19 at 16:10
Possible duplicate of [How do I limit the number of rows returned by an Oracle query after ordering?](https://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering) — Peter Šály, Mar 14 '19 at 16:11
https://stackoverflow.com/questions/1239723/how-do-you-do-a-limit-query-in-hql — Prashant.jha, Mar 14 '19 at 16:11
adding FETCH FIRST 5 ROWS ONLY; to end, didnt work either : ( — Clomez, Mar 14 '19 at 16:15

score 2 · Accepted Answer · answered Mar 14 '19 at 16:13

2

Try:

@Query(value = "SELECT A, B ... FROM ADMIN.SUPER_SEARCH WHERE A = :param OR B = :param AND rownum <= 5 ORDER BY A desc", nativeQuery = true )

answered Mar 14 '19 at 16:13

You are welcome! Please consider accepting the answer if it answered your question. – Diego Victor de Jesus Mar 14 '19 at 16:18

1 Answers1