CODEWITHSUNDEEP
sqloracle-databaseanalytic-functions
raiym
raiym

Reputation: 1499

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

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')

Upvotes: 0

Views: 333

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

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;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270553

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;

Upvotes: 4

Related Questions