Archives of the TeradataForum

Message Posted: Thu, 01 May 2008 @ 20:58:26 GMT

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

Hi Victor:

There seems to be some confusion as to what is happening to Megan. The issue is not the WHEN - that is working fine. The issue is the WHERE clause. If the WHERE is in the SELECT within the view, only rows within the desired timeframe are ranked with the correct rows being returned. However, if the same WHERE is on the SELECT from the view, all rows are ranked first within the view (even prior to the period desired) and then the WHERE is applied to that interim answerset. As a result, some of the desired rows are not returned because they are lower than another value which exists prior to the desired period.

In other words, the WHERE in the view is working correctly before the ranking operation and as expected. It would be fine to put it in the view if Megan wanted it to be a static period. However, since she needs it to move over time, the view would need to be replaced whenever a different period is needed. Where as, if the WHERE in the SELECT from the view were applied earlier instead of later, the same results would be obtained and the query is dynamic.

To make it work the way she needs it to work, I suggested that she use a "date table" with the single row to be product joined to the actual table for the WHERE within the view. This would make the view dynamic by using only the dates within the table and doing it prior to the ranking operation. The real question is whether or not the optimizer is acting correctly in its choice regarding the timing of applying the WHERE conditions. Otherwise, why does the optimizer not always read all rows into spool and then select from spool based on the select from the view.

I am just saying that this approach of when to apply the WHERE with the STAT function seems contrary to the way it works without a STAT function. Since the original RANK and the new RANK/OVER work the same, it seems to be by design. This is a bit scary when working with very large tables to evaluate them all first and then eliminate what is not needed. Maybe I have missed this in a previous discussion thread (if so, I apologize), but a confirmation for Megan of whether or not it is correct from the GSC might be helpful to Megan so that she can plan SQL for her application going into the future.

Hope this helps clear up the confusion.

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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