Home / Q&A / FETCH or LIMIT in a Subquery
You can translate the question and the replies:

FETCH or LIMIT in a Subquery

Are you not able to use FETCH and/or LIMIT in a subquery? This query throws an error - Syntax error: Exception parsing query near 'LIMIT' SELECT a.* FROM view1 INNER JOIN (SELECT joinval from view2 ORDER BY joinval DESC FETCH FIRST 3 ROWS ONLY) AS view3 ON view1.joinval = view3.joinval Also fails for LIMIT 3 instead of FETCH
FETCH LIMIT Subquery
user
17-04-2020 13:43:18 -0400

1 Answer

Hi, I was able to do it as follows since LIMIT and FETCH are currently not supported in subqueries in Denodo. Here the steps: 1. Create a derived view with an order by on joinval, e.g. CREATE VIEW VIEW2_ORDERED AS SELECT \* FROM VIEW2 ORDER BY JOINVAL DESC 2. Create a derived view using ordered view and adding a rank column, e.g. CREATE VIEW VIEW2_RANKED AS SELECT \* FROM VIEW2_ORDERED, ROWNUM() AS RANK 3. Filter this ranked view, e.g. SELECT \* FROM VIEW1 INNER JOIN (SELECT JOINVAL FROM VIEW2_RANKED WHERE RANK IN (1, 2, 3)) VIEW3 ON VIEW1.JOINVAL = VIEW3.JOINVAL Hope this helps!
Denodo Team
20-04-2020 15:00:05 -0400
You must sign in to add an answer. If you do not have an account, you can register here