ORA-00904: "PREV_TEMP": invalid identifier with LAG function

Question

Whats is wrong with this query?

It returns: ORA-00904: "PREV_TEMP": invalid identifier

SELECT Id, RecordDate, Temperature, LAG(Temperature) OVER (ORDER BY RecordDate) as prev_temp
FROM Weather
WHERE Temperature > prev_temp;

SQL schema:

Create table If Not Exists Weather (Id int, RecordDate date, Temperature int)
Truncate table Weather
insert into Weather (Id, RecordDate, Temperature) values ('1', '2015-01-01', '10')
insert into Weather (Id, RecordDate, Temperature) values ('2', '2015-01-02', '25')
insert into Weather (Id, RecordDate, Temperature) values ('3', '2015-01-03', '20')
insert into Weather (Id, RecordDate, Temperature) values ('4', '2015-01-04', '30')

score 4 · Answer 1 · answered May 24 '19 at 11:55

4

What is wrong with the query is that column aliases cannot be re-used in the SELECT, WHERE, FROM, or GROUP BY clauses where they are defined. This applies to window functions, as well as everything else. And this is a rule in SQL, not Oracle (although some databases relax the restriction on GROUP BY).

In your case, there are basically two solutions, a subquery and a CTE:

WITH w AS (
      SELECT w.*,
             LAG(Temperature) OVER (ORDER BY RecordDate) as prev_temperature
      FROM weather w
     ) 
SELECT Id, RecordDate, Temperature,  prev_temp
FROM w
WHERE Temperature > prev_temp;

answered May 24 '19 at 11:55

Gordon Linoff

1,242,037
58
646
786

There are really TWO issues. In many cases, you can work around an alias issue without subqueries, by simply repeating the expression as is in the `WHERE` clause (or the other clauses). Alas, that's not the case for analytic functions - even without an alias, they can't be used in other clauses (except `ORDER BY`, which is evaluated after `SELECT`). Note also that "subquery and CTE" are not really two solutions - a CTE is a "factored **subquery**". – May 24 '19 at 12:47
I also doubt that any databases relax the restriction on aliases specifically for `GROUP BY`. Can you suggest any examples? Or are you mixing it up with `ORDER BY`? – May 24 '19 at 12:49
@mathguy . . . MySQL (and hence MariaDB), BigQuery, Hive, Postgres (and hence a bunch of Postgres-derived databases). It is pretty standard practice for non-legacy databases. – Gordon Linoff May 25 '19 at 01:23

Barbaros Özhan · Accepted Answer · 2019-07-26T08:30:45.240

2

You cannot use directly, but need to use in a subquery to be able to use the returning value from analytic function

SELECT *
  FROM
  (
   SELECT Id, RecordDate, Temperature, 
          LAG(Temperature) OVER (ORDER BY RecordDate) as prev_temp
     FROM Weather
  )
  WHERE Temperature > prev_temp;

edited Jul 26 '19 at 08:30

answered May 24 '19 at 11:51

Barbaros Özhan

59,113
10
31
55

Thank you, this works. Could you please explain why we should use subquery and why query listed in question does not work? – raiym May 24 '19 at 11:54
@raiym you're welcome. Since they're computed columns depending on the related columns of the table. i.e they're results actually. – Barbaros Özhan May 24 '19 at 12:20
2

I would say "because they are computed columns depending on related **rows** of the table". This is really the central point of the issue; if the computations depended only on other columns (within the same row), there would be no issue using the expression in `WHERE` (but, of course, without an alias - the expression would have to be repeated). Because analytic functions depend on other **rows**, they **can't** be used in `WHERE` at the same level as the `SELECT` even if they are spelled out in full rather than by alias. – May 24 '19 at 12:52

ORA-00904: "PREV_TEMP": invalid identifier with LAG function

2 Answers2