Missing window specification for this function

Question

Now getting the following error:

ORA-30484: missing window specification for this function
30484. 00000 -  "missing window specification for this function"
*Cause:    All window functions should be followed by window specification,
           like <function>(<argument list>) OVER (<window specification>)
*Action:
Error at Line: 17 Column: 72

Updated code (as per Aaron Hall suggestion)

select meter_id,
to_char(interval_time_local,'dd-mon-yyyy hh24:mi') as gap_start,
to_char(next_interval_time_local,'dd-mon-yyyy hh24:mi') as gap_finish
from (
                select i1.device_id as meter_id
                          ,interval_time_local
                          ,SUM(CASE i1.chan_num WHEN 0 THEN i1.chan_value * 2 ELSE 0 END) as chan0
                          ,SUM(CASE i1.chan_num WHEN 2 THEN i1.chan_value * 2 ELSE 0 END) as chan1
                          ,SUM(CASE i1.chan_num WHEN 1 THEN i1.chan_value * 2 ELSE 0 END) as chan2
                          ,SUM(CASE i1.chan_num WHEN 3 THEN i1.chan_value * 2 ELSE 0 END) as chan3
                          ,lead(interval_time_local,1) over (order by device_id, interval_time_local) as next_interval_time_local
                from gn_owner_demo.ami_read_interval i1
                where created_utc = ( select max(created_utc)
                                                                from gn_owner_demo.ami_read_interval i2
                                                                where i2.device_id = i1.device_id
                                                                and i2.interval_time_local = i1.interval_time_local)
                group by device_id, interval_time_local, i1.device_id, lead(interval_time_local,1)
                order by device_id, interval_time_local
                )
where interval_time_local <> next_interval_time_local - 30/1440;

I have inherited some code that extracts gaps in data. I have never used the OVER clause so not sure if it is used correctly. The error message is:

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:
Error at Line: 11 Column: 134

Here is the query - I am running in SQL developer:

select meter_id
,to_char(gn_owner_demo.interval_time_local,'dd-mon-yyyy hh24:mi') as gap_start
,to_char(gn_owner_demo.next_interval_time_local,'dd-mon-yyyy hh24:mi') as gap_finish
from (
select i1.device_id as meter_id
    ,interval_time_local
    ,SUM(CASE i1.chan_num WHEN 0 THEN i1.chan_value * 2 ELSE 0 END) as chan0
    ,SUM(CASE i1.chan_num WHEN 2 THEN i1.chan_value * 2 ELSE 0 END) as chan1
    ,SUM(CASE i1.chan_num WHEN 1 THEN i1.chan_value * 2 ELSE 0 END) as chan2
    ,SUM(CASE i1.chan_num WHEN 3 THEN i1.chan_value * 2 ELSE 0 END) as chan3
    ,lead(gn_owner_demo.interval_time_local,1) over (order by gn_owner_demo.device_id, gn_owner_demo.interval_time_local) gn_owner_demo.next_interval_time_local
from gn_owner_demo.ami_read_interval i1
where gn_owner_demo.created_utc = ( select max(gn_owner_demo.created_utc)
            from gn_owner_demo.ami_read_interval i2
            where i2.device_id = i1.device_id
            and i2.interval_time_local = i1.interval_time_local)
group by gn_owner_demo.device_id, gn_owner_demo.interval_time_local
order by gn_owner_demo.device_id, gn_owner_demo.interval_time_local
)
where gn_owner_demo.interval_time_local <> gn_owner_demo.next_interval_time_local - 30/1440;

Answer 1

What is gn_owner_demo.interval_time_local?

Your from clause references the gn_owner_demo.ami_read_interval so I assume that gn_owner_demo is the schema name and ami_read_interval is the name of the table. If that is correct, though, that implies that gn_owner_demo.interval_time_local is referring to a separate table, interval_time_local also owned by gn_owner_demo. If that is actually a separate table, you'd need to join to that table. My guess, though, is that you really want to reference the interval_time_local column in the gn_owner_demo.interval_time_local table and that applies to the other columns you're referencing this way in which case you'd use the il alias, i.e.

lead( il.interval_time_local, 1 )
   over( order by il.device_id,
                  il.interval_time_local) as next_interval_time_local

Now, I'd suspect that you really want to partition by the il.device_id rather than having it in the order by but since you haven't told us what your data looks like or what your desired results are, that's just speculation on my part.

If my guess is correct, you'd also need to alter the WHERE, GROUP BY, and ORDER BY clauses in a similar fashion so that you're referencing column names from a table you're querying rather than table aliases.