Search code examples
sqlselect

row_number() FROM keyword not found where expected

I have an sql-statement, which should select all content of a table ordered by the row number. So i used the row_number function.

But I get everytime the error

Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected

or this error

ORA-00936: missing expression

My statement looks like this:

SELECT row_number() OVER(ORDER BY table.column1) AS row, table.* 
FROM table 
WHERE column2= ("Any String") 
ORDER BY row;

I hope you can help me with this code.

Solution

  • If you're trying to use the name row for that result column, you'll have issues since it's a Reserved Word in Oracle. Either pick a different name for the column, or surround it with "" quotes (a quoted identifier:

    SELECT row_number() OVER(ORDER BY table.column1) AS "row", table.* 
FROM table 
WHERE column2= ("Any String") 
ORDER BY "row";

    (I'd generally opt for picking a different name though)