Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Thu, 01 May 2008 @ 09:23:51 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

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.


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020