Archives of the TeradataForum
Message Posted: Thu, 01 May 2008 @ 09:23:51 GMT
| Subj: | | Re: Retrieving the Second Highest Amount |
| |
| From: | | Victor Sokovin |
| | 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. | |
I am far from the database at the moment and could not try any examples myself but from what I have understood from the discussion so far the
above behavior is correct. If the WHEN clause is inside the view definition then it is evaluated first and the ranking is done over the period of
time specified by the WHEN clause. If the WHEN goes outside of the view, then the view will have to do the ranking globally (over the entire
period of five years) and only after that the external WHEN will apply, which may result in a totally different answer set: some customers may
have a few orders (let's assume we are talking about orders here) after March 1 but if they used to place larger orders before March 1 then the
global #2 ranking would be dated prior to March 1 (that's in the result of the "global" view) and the subsequent external WHEN (date >= March
1) will eliminate such customers.
| | 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. | |
I'd also mention one hypothetical view-based solution. Not sure it is a practical one. If the number of customers is not too high it could be
considered.
Let's assume that the periods are of type "date >= start_date".
The ranking the OP is talking about can change any day so one could consider building a view which would provide the answer for each customer
and ***each start_date*** from 2003 to, say, 2020 (or whatever date in the future).
I am not going to write SQL without a database to test it on but the idea is to join the OP's table to the (system) calendar table and
calculate the ranking for each calendar date as start_date. Hopefully, this can be realized as no dynamic parameters need to be submitted to the
view. The downside is obvious. The view would generate (too) many rows per customer but I have a feeling that this is the only possible view-based
solution of this kind.
Victor
| 
