I have a table with the columns:date1,name and price. What i want to do is to add 2 columns one right having the minimum and maximum dates of the consecutive dates of the same name.
I have written the following query that explains the rule:
when lag(name,1) over(order by date1 ASC,name ASC)=name then lag(minDate,1) over(order by date1 ASC,name ASC)
end as minDate,
when lag(name,1) over(order by date1 DESC,name DESC)=name then lag(maxDate,1) over(order by date1 DESC,name DESC)
end as maxDate
from MyTable order by date1 ASC,name ASC
My problem is that i get an "invalid context for minDate/maxDate" (SQLCODE=-206, SQLSTATE=42703) Why can't i refer to a calculated column? Is there any other way?
a.name1, a.date, a.price, b.first_date, b.last_date
inner join (
select a2.name1, min(a2.date) as first_date, max(a2.date) as last_date
from mytable a2
group by a2.name1
) as b
on a.name1 = b.name1