sqloracle-databasesyntax-errorrow-numberora-00936

ORA - 00936 error but anable to find what is missing in line 2


I'm trying to execute this code but it says i'm missing something in row_number() expression which I'm not able to figure out at all.

with summary as 
(select s.city, length(s.city) as C_length, 
row_number() over (partition by length(s.city), order by s.city)
as r1 from station s
where 
length(s.city) = (SELECT min(LENGTH(s1.CITY)) FROM STATION s1)
or length(s.city) = (SELECT max(LENGTH(s2.CITY)) FROM STATION s2))
select su.city, su.C_length 
from summary su;

Solution

  • Your query can be simplified:

    with summary as (
          select s.city, length(s.city) as C_length, 
                 min(length(s.city)) over () as min_length,
                 max(length(s.city)) over () as max_length,
          from station s
         )
    select su.city, su.C_length 
    from summary su
    where c_length in (min_length, max_length);
    

    I removed r1 because it is not being used.