Oracle SQL : FROM keyword not found where expected

Question

my query as follows.

WITH CTE AS
(
    SELECT EmpID, EmpName, EmpSalary,
           RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)
    FROM dbo.Salary
)
SELECT EmpID, EmpName, EmpSalary
FROM CTE
WHERE RN = @8thRow

I am getting error as

Error report - SQL Error: ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected".

Can someone explain what is wrong with the code?

I think you are missing some of the code would you please include the initial part of the code as it is difficult to get as to what your query is — Himanshu Ahuja

Littlefoot Littlefoot · Accepted Answer · 2018-11-12T07:09:44

Wrong syntax; an example based on Scott's schema:

SQL> select ename, sal from emp order by sal;

ENAME             SAL
---------- ----------
SMITH             920
JAMES             950
ADAMS            1100
WARD             1250
MARTIN           1250
MILLER           1300
TURNER           1500    -- 8th  --> you need this one
ALLEN            1600
CLARK            2450
BLAKE            2850
JONES            2975    -- ...
SCOTT            3000    -- 3rd
FORD             3000    -- 2nd
KING            10000    -- 1st, when sort is DESCending

14 rows selected.

SQL> with cte as
  2    (select empno, ename, sal,
  3       row_number() over (order by sal desc) rn
  4     from emp
  5    )
  6  select empno, ename, sal
  7  from cte
  8  where rn = 8;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7844 TURNER           1500

SQL>

Oracle SQL : FROM keyword not found where expected

1 Answers