CODEWITHSUNDEEP
oracle-databaseplsqllag
WhyHelloJessie
WhyHelloJessie

Reputation: 21

Oracle lag function, can it accept a column alias?

I am trying to use the lag function so I can compare one column to the last without using a cursor. However the column I need to compare against has to go by an alias as I am using 3 unions). Here is an example of what I am up to.

SELECT
'Y' AS paid,
 lag(paid,1) over (ORDER BY salary) AS prev_paid
FROM pay
UNION 
SELECT 
  'N' as paid,
  lag(paid,1) over (ORDER BY salary) AS prev_paid
FROM not_paid

I keep getting the Error: PL/SQL: ORA-00904: "paid": invalid identifier

Upvotes: 2

Views: 1813

Answers (2)

Allan
Allan

Reputation: 17429

The general answer is no: in Oracle you can never use a column alias at the level where it is defined, except in order by clauses.

However, your query has other issues, since you're getting the lag value of a constant. @Tony Andrew's query seems like what you actually want.

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132650

I suspect you want something more like this:

SELECT paid, lag(paid,1) over (ORDER BY salary) AS prev_paid
FROM
(
  SELECT 'Y' as paid, salary
  FROM pay
  UNION 
  SELECT 'N' as paid, salary
  FROM not_paid
)

Upvotes: 5

Related Questions