Limit Clause SQL for ORACLE not working
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Connectors
- Database Connection
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Can you try using the limit option in the configuration window rather that in your query?
 
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The FETCH/OFFSET syntax did not work for me.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Does the rownum work for you? This is effectively limiting the number of rows by whatever value follows.
