"Gert van der Kooij" <gk-ibm-db2@xs4all.nl> wrote in message
news:MPG.196400ca76e09c8989885@news.xs4all.nl...
[sni]
> >
> > without using the days() function
> >
> > SELECT MIN(due_date) OVER(ORDER BY days(DUE_DATE)
> > RANGE BETWEEN 60 PRECEDING AND CURRENT ROW )
> >
> > which works, but that query isn't satisifed by DB2 directly from the an
index
> > on DUE_DATE so I get a tablescan (of a sorted temp table).
> >
> > I guess I need a generated column days(DUE_DATE) that I can put in the
> > index...
> >
>
> If I understand the RANGE option right it doesn't restrict the rows
> returned so it seems normal to use a tablescan. Don't you need a where
> clause on this query?

The query has to process rows in DUE_DATE order, I've got an index on DUE_DATE
with the intent of avoiding the sort. If I have no where clause, I'll get an
index scan followed by table lookups which is what I want. The hassle is that
the days() function does not use the index.

I'm 99% sure that I can't x DAYS / x MONTHS / x YEARS ... in the RANGE clause,
and it's just a little bit of a shame considering that one big motivation for
the OLAP functions is the ability to calculate things such as 60-day moving
average...

Regards
Paul Vernon
Business Intelligence, IBM Global Services