Limit Clause SQL for ORACLE not working

[jaipersr]

Hi all,

I am trying to do something simple. I am using an input tool to connect to an sql database. The tool works just fine. However, when I add a limit clause to the end of my SQL to restrict the number of records being pulled in the sql errors. Am I doing something wrong here? I also tried the Fetch syntax but that does not seem to work either. Any help would be appreciated.

[BrandonB · Accepted solution]

Can you try using the limit option in the configuration window rather that in your query? 

 

[jaipersr]

I completely missed that. Thank you for reminding me of its existence. But I still think its weird how the limit clause does not work in the input tool. Is this a known error that I just have to accept?

[BrandonB]

Syntax for Oracle is a bit different, so you wouldn't be using the traditional LIMIT. 

You can use 

select * 
from sometable
where rownum <= 10

or 

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

Just to clarify, this is because Oracle doesn't use LIMIT

[jaipersr]

The FETCH/OFFSET syntax did not work for me.

[BrandonB]

Does the rownum work for you? This is effectively limiting the number of rows by whatever value follows.