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

Question

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;

You are not using `r1` so there is no need to calculate it. – Gordon Linoff Feb 22 '17 at 20:31 — Gordon Linoff, Feb 22 '17 at 20:31

score 2 · Answer 1 · answered Feb 22 '17 at 20:24

2

There shouldn't be a comma between the partition by clause and the order by clause. Just remove it and you should be OK:

row_number() over (partition by length(s.city) order by s.city)
-- Comma removed here ------------------------^

answered Feb 22 '17 at 20:24

Mureinik

297,002
52
306
350

1

Thank you very much. will do so – gireesh4manu Feb 22 '17 at 20:34

score 1 · Accepted Answer · answered Feb 22 '17 at 20:31

1

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.

answered Feb 22 '17 at 20:31

Gordon Linoff

1,242,037
58
646
786

Thank you. so min(length(s.city)) over () as min_length would work ? can you explain what funtion is called suring "over ()" please ? – gireesh4manu Feb 22 '17 at 20:35
1

@Vish . . . I don't understand your comment. The code seems pretty clear: it is calculating the minimum length of the city over all the data. – Gordon Linoff Feb 23 '17 at 03:37
I meant that the paranthesis was not enclosing anything inside it right ? – gireesh4manu Feb 24 '17 at 18:45
@Vish . . . That is the syntax for a window/analytic function. – Gordon Linoff Feb 25 '17 at 04:40

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

2 Answers2