Archives of the TeradataForum
Message Posted: Wed, 30 Apr 2008 @ 21:30:35 GMT
Subj: | | Re: Retrieving the Second Highest Amount |
|
From: | | Michael Larkins |
OK Megan:
I now see the issue. When a WHERE is on the SELECT in the view, it is applied before the OLAP/STAT function (rows returned as expected) and
when the WHERE is on the SELECT from the view, it is applied after the OLAP/STAT operation(can see this in an EXPLAIN)(no rows returned). It
would appear to be a bug and you should probably report it to the GSC.
To get around it, you will either need to use the macro suggestion that I gave you earlier and pass a start and end date to the SELECT in the
macro. Otherwise, you could create a table that contains one row and the row contains the start and end dates. Then you can join the real table
and your date table together to eliminate rows that are not needed before doing the STAT function.
Hope this helps,
Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor
|