This content has been marked as final. Show 11 replies
Allow me to rephrase it
I am so used to MS SQL and MYSQL, so i am trying to run SELECT * but limit the result to only 10 rows
So the understated is my query
SELECT * FROM ORCHP01DBO.WGN3EVENT LIMIT 10 ;
ORA-00933: SQL command not properly ended
Obviously SQL for Oracle is different, and i am not too sure how to correct it.
You're in luck, we actually have a translator for you to try.
Tools > Migration > Translation Scratch Editor
Set your first panel to SQL Server.
Type your query.
Click the translate button.
In 11gR2 and prior versions, we roughly emulate that behavior with a pseudo-column named 'ROWNUM'
WHERE ROWNUM < 11 -- that would give you the 'first' 10 rows
apologize, I think what you're doing is closer to MySQL syntax, but you should get the general gist now.
Use the WHERE ROWNUM < to get close to the results you want.
Note that row order isn't guaranteed in Oracle.
For a much better overview of this technique, check out this article from Tom Kyte
Edited by: Jeff Smith SQLDev PM on Oct 26, 2012 11:55 AM
Use the WHERE ROWNUMBER < or > to get close to the results you want.
That's not correct. The psuedo-column to use is ROWNUM, not ROWNUMBER.
And you CANNOT use > as that operator doesn't work with rownum. As Tom says in the article you linked to
Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:
where ROWNUM > 1;
Because ROWNUM > 1 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1.