We are using Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
I have one doubt about lag function usage - When I am executing below query it is throwing an error -
with tmp (id, visit_date, people) as
(select 1, to_date('2017-01-01','yyyy-mm-dd') , 10 from dual union all
select 2 , to_date('2017-01-02','yyyy-mm-dd'),109 from dual union all
select 3 , to_date('2017-01-03','yyyy-mm-dd'),150 from dual union all
select 4 , to_date('2017-01-04','yyyy-mm-dd'),99 from dual union all
select 5 , to_date('2017-01-05','yyyy-mm-dd'),145 from dual union all
select 6 , to_date('2017-01-07','yyyy-mm-dd'),1455 from dual union all
select 7 , to_date('2017-01-08','yyyy-mm-dd'),199 from dual union all
select 8 , to_date('2017-01-09','yyyy-mm-dd'),188 from dual)
select lag(visit_date) over (order by visit_date range 1 preceding)
,id
,visit_date
,people
from tmp
Error - ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
But once I change lag to count - it is working fine.
I am not able to understand why lag is not working with window clause?
Anybody can explain why it is not working.
Thanks in advance.