ORA-00904: "RN": Invalid Identifier

Question

Can anyone please explain to me why I'm getting this error, where I'm wrong and what to do in future to avoid it.

SELECT a.*, row_number() over (partition by code_employee order by  
to_date(substr(last_located_time,1,10),'yyyy-mm-dd')) rn
FROM
    (SELECT LAST_LOCATED_TIME, NAME_COMMON, CODE_EMPLOYEE
FROM 
     daily_report

 WHERE INSERT_DATE = TRUNC (SYSDATE-3) AND DEVICE_NAME LIKE '%SM-T116IR')a 
 WHERE rn=1;

http://stackoverflow.com/questions/3451534/how-do-i-do-top-1-in-oracle — PM 77-1, May 19 '17 at 17:29

Barbaros Özhan · Answer 1 · 2017-10-10T17:07:34.593

1

please try this one :

select *
  from (select a.*,
               row_number() over(order by to_date(last_located_time, 'dd.mm.rrrr')) rn
          from (select last_located_time, name_common, code_employee
                  from daily_report                
                 where insert_date = trunc(sysdate-3) and device_name like '%SM-T116IR') a)
 where rn = 1

edited Oct 10 '17 at 17:07

answered May 19 '17 at 20:41

Barbaros Özhan

59,113
10
31
55

Ozhan thanks for the input.. can somebody plz explain me this query as I'm trying to understand the code written by someone.... `row_number() over (partition by code_employee order by to_date(substr(last_located_time,1,10),'yyyy-mm-dd'))` – Toddler May 20 '17 at 05:50
You're welcome Toddler. By the way, you'd better use "substr(to_char(nd_sonokuma,'yyyy-mm-dd'),1,10)" for "order by" clause. Since as you know, "substr" should be applied to a string, not to a date. – Barbaros Özhan May 20 '17 at 07:12
ozhan but the guy used `substr` to `nvarchar2` column and then convert it `to_date` which dosen't make sense to me since he is not using it as date. – Toddler May 20 '17 at 08:58

ORA-00904: "RN": Invalid Identifier

1 Answers1