How to use Oracle's SAMPLE() and ROWNUM with QueryDSL-SQL?

[Максим Смоляков]

Hi all!

My Oracle query retrieves 5 random rows from large dataset:

SELECT users.userid
  FROM users SAMPLE(1)
 WHERE ROWNUM <= 5

And I want to implement this case with QueryDsl something like this:

query()
        .select(users.userid)
        .from(users)
        .fetch();

Does QueryDsl supports Oracle's SAMPLE() and ROWNUM syntax?

[Himanshu Upadhyay]

Hi, I am trying to implement the same using QueryDSL, din't find any alternates,let me know if you. found any. solution. for it.

[Jan-Willem Gmelig Meyling]

For the `SAMPLE(1)` expression you can use a JoinFlag with a TemplateExpression (Expressions.stringTemplate(...) for example).

For the row num you can use a plain template expression: .where(Expressions.numberTemplate(Integer.class, "ROWNUM").lte(5))`

Op woensdag 22 juli 2020 om 20:58:06 UTC+2 schreef upadhyay...@gmail.com: