Oracle Sql Delete with row number throws error ORA-00904: "RN": invalid identifier

Question

I am trying to delete from a table using select & row number but it is throwing me error at the last line.

delete from ZZ_temp_Value where (nach_id,vv_lfd,language,value,dq_nr,emptyvalue,unit) IN 
(select  t.*,ROW_NUMBER() OVER (PARTITION BY vv_lfd ORDER BY emptyvalue,position) rn 
     from 
       (select distinct w.*, q.position 
        from ZZ_temp_Value w, zz_def d, dv_format q 
         on q.nach_id = w.nach_id
        and q.vv_lfd = w.vv_lfd
      order by  w.emptyvalue, q.position)t
       where t.rn < 1 ;

Or i wanted to delete the value from this select in table ZZ_temp_Value where the row number is not 1.

select  t.*,ROW_NUMBER() OVER (PARTITION BY vv_lfd ORDER BY emptyvalue,position) rn 
         from 
           (select distinct w.*, q.position 
            from ZZ_temp_Value w, zz_def d, dv_format q 
             on q.nach_id = w.nach_id
            and q.vv_lfd = w.vv_lfd
          order by  w.emptyvalue, q.position

the last line of `where t.rn < 1` translates to less than 1 – etch_45 Nov 20 '20 at 14:49 — etch_45, Nov 20 '20 at 14:49
Please post exact error message. – Parfait Nov 20 '20 at 14:58 — Parfait, Nov 20 '20 at 14:58

score 0 · Answer 1 · answered Nov 20 '20 at 16:00

Several syntax and logic issues abound in your attempted query:

ON Clause: Using an ON clause without a matching JOIN. Your query can possibly work if ON is replaced by WHERE which is the older, deprecated way of joining tables in SQL known as the implicit join.
Cross Join: As is the drawback of the implicit join, you mistakenly (or intentionally?) did not join zz_def d with matched columns. While you match q and w, not matching d rendered a cross join on that table to return more rows than desired. Also, d is not used in SELECT.
Misaligned Subquery: Layered subqueries show a missing closing parenthesis where you misaligned table alias t that references calculated column, rn, at a different level.
WHERE Logic: Since ROW_NUMBER returns positive values, your logical condition t.RN < 1 may return no results once syntax errors are resolved.

Additionally, consider a few tips:

Explicit Join: Use the explicit JOIN to join tables and avoid the subtle mistake of running a cross join.
SELECT *: Avoid using SELECT * to better control presentation and visibly show selected columns. Specifically, no where are the following columns shown in subquery: nach_id, language, value, dq_nr which you intend to match to outer query.
Column Aliases: Alias all columns in SELECT to visibly show sources to tables. Specifically, your window function does not explicitly reference table sources of columns. Ideally, you would run PARTITION BY w.vv_lfd ORDER BY w.emptyvalue, q.position.
Table Aliases: Use better table aliases than the letters chosen to avoid code readers to scan back to FROM and JOIN clauses for their original sources. Ideally,
```
from ZZ_temp_Value t ... zz_def d ... dv_format f

from ZZ_temp_Value tmp ... zz_def def ... dv_format fmt
```

Assuming your ultimate need is to delete rows where the row number is greater than 1 really (rn > 1), consider adjusted query (that would need testing).

DELETE FROM ZZ_temp_Value 
WHERE (nach_id, vv_lfd, language, value, dq_nr, emptyvalue, unit) IN 
  (SELECT t.nachi, t.vvlfd, t.language, t.value, t.dq_nr, t.emptyvalue, t.unit 
   FROM 
     (SELECT tmp.nachi, tmp.vv_lfd, tmp.language, tmp.value
             , tmp.dq_nr, tmp.emptyvalue, tmp.unit 
             , ROW_NUMBER() OVER (PARTITION BY tmp.vv_lfd 
                                  ORDER BY tmp.emptyvalue, fmt.position) rn
      FROM ZZ_temp_Value tmp
      INNER JOIN dv_format fmt
          ON  fmt.vv_lfd = tmp.vv_lfd
          AND fmt.nach_id = tmp.nach_id
      -- CROSS JOIN zz_def d                     -- CAREFULLY THINK ABOUT THIS! 
     ) t
   WHERE t.rn > 1
  );

DISTINCT and ORDER BY have been removed being redundant. ROW_NUMBER() would repeat for duplicate records and subquery ordering of rows is ignored for outer query results.

Hi, thanks for the answer but i get error in the line -- > where t.rn > 1 since we are not selecting t.rn in the select statement it throws an error saying ORA-00904: "RN": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: — Sangathamilan Ravichandran, Nov 23 '20 at 08:27
Odd. I know Oracle has rules of deeply nested subqueries but as positioned, `rn` should exist for `WHERE` condition (which logically runs before `SELECT`). Try running the subquery (i.e., two sub-`SELECT`s) by itself outside of `DELETE`. Same error? — Parfait, Nov 23 '20 at 18:08

Oracle Sql Delete with row number throws error ORA-00904: "RN": invalid identifier

1 Answers1