Archives of the TeradataForum

Message Posted: Fri, 09 Jul 2004 @ 07:16:58 GMT

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

All,

We recently upgraded our Teradata system from 4.0.1 to 5.1. We have some views defined with Rank Function. These views are used in business objects whose result set is limited by a filter on the date column. Prior to the upgrade, the explain showed the filter applied first and then the RANK applied. This is how we wanted the view to work. However, after the upgrade, the RANK function inside the subquery is applied first and then the filter is applied.

     /* This query in a simple way mimics our real query. After the upgrade,
     the condition a.col2 = 2 is applied after ranking the subquery.*/

     select * from
     (select
     a.col1, a.col2, a.col3
      from dropme a
     group by a.col1 qualify Rank(a.col2 desc, a.col3 asc)   = 1) a
     where a.col2 = 2

     /*
     -- Consider this script to recreate the problem:
     --drop table iconstaging.dropme;
     create table iconstaging.dropme
     (
     col1 varchar(25), col2 int, col3 int
     );
     insert into iconstaging.dropme values ('abc',1,1);
     insert into iconstaging.dropme values ('abc',2,3);
     insert into iconstaging.dropme values ('abc',2,2);
     insert into iconstaging.dropme values ('abc',3,2);
     insert into iconstaging.dropme values ('abc',3,3);
     */

With the above data, I am getting no values for my query. In this case, the Rank Function chooses 3 for col2 and 2 for col3. However, the filter from outer query specifies col2 = 2. Since it does not exist, I get no result.

Seems like I get the right results with the following query:

     select * from
     (select
     a.col1, a.col2, a.col3
      from iconstaging.dropme a
     where a.col2 = 2
     group by a.col1 qualify Rank(a.col2 desc, a.col3 asc)   = 1) a

     ------------
     Result Set
     ------------
             col1    col2    col3
             abc     2       2

However, I cant use this because the criterion has to be dynamically specified outside the subquery

Is there a different method to rewrite this subquery without using RANK function? I tried using aggregate functions (max and min), but they are not suitable to me, because the the real query has more columns listed in RANK() and has some product joins in the from clause. Any help is appreciated.

Thanks in advance.

-----------------------------------
Bhupesh Singu
Database Administrator
-----------------------------------

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