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;

score 2 · Answer 1 · answered Jun 02 '14 at 01:09

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.

Hi - thanks for your quick reply... that was an error... have received this updated query from query owner - you are right, schema is "gn_owner_demo". interval_time_local is a column in ami_read_interval table so that shouldn't be in the query. — user3697763, Jun 02 '14 at 01:23
and I should add... the data holds data by device, and the query is meant to identify any gaps in the data. as in, each device should have 48 rows of data per day from the date the device is active until it is retired. The query is meant to identify any points in which there is less than 48 rows of data and/or entire days of data missing. It should then report that divice X has data for 1/5/14 #4 but then subsequent data is not 1/5/14 #5 but say 15/5/14 #10. Does that help? — user3697763, Jun 02 '14 at 01:26
have removed the erroneous schema identifiers and attempted to run this: — user3697763, Jun 02 '14 at 01:27

Missing window specification for this function

1 Answers1