sql - Calculated column reference in DB2 -

sql - Calculated column reference in DB2 -

i have table columns:date1,name , price. want add together 2 columns 1 right having minimum , maximum dates of consecutive dates of same name.

i have written next query explains rule:

select date1,name,price case when lag(name,1) over(order date1 asc,name asc)=name lag(mindate,1) over(order date1 asc,name asc) else date1 end mindate, case when lag(name,1) over(order date1 desc,name desc)=name lag(maxdate,1) over(order date1 desc,name desc) else date1 end maxdate mytable order date1 asc,name asc

my problem "invalid context mindate/maxdate" (sqlcode=-206, sqlstate=42703) why can't refer calculated column? there other way?

it's complaining lag(maxdate,1), because maxdate isn't defined in scope; it's not column in mytable, , aliases aren't available until after select list finishes in db2 (they become available pushing subquery, or in clauses having).

incidentally, query can improve written following:

select date1, name, price, lag(date1, 1, date1) over(partition name order date1) mindate, lead(date1, 1, date1) over(partition name order date1) maxdate mytable order date1, name

(i've left out asc, it's default ordering)

lead(...) opposite function lag(...), , looks 1 row ahead. using both functions way allows optimizer compute 1 window (what's specified in over(...)). the 3rd parameter windowing functions here default value - in case there wasn't next/previous row, returns date1 current row. partition by grouping windowing function, , takes place of case ... when ... in original query. (in general, find such constructs reflect mentality mutual imperative programming, tends run counter set-based nature of sql. there improve ways things)

sql db2