All Forums General
zulfi123786 2 posts Joined 11/14
06 Jun 2016
Order of execution in Qualify clause
CREATE MULTISET VOLATILE TABLE V_CURRENT_DATE
(
ID integer,
ID_VALUE VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC 
)PRIMARY INDEX(ID)ON COMMIT PRESERVE ROWS;

insert into V_CURRENT_DATE
values (10,'one');
insert into V_CURRENT_DATE
values (11,'two');
insert into V_CURRENT_DATE
values (12,'three');
insert into V_CURRENT_DATE
values (13,'four');
insert into V_CURRENT_DATE
values (14,'five');

select * from V_CURRENT_DATE
qualify
row_number() over(partition by 'A' order by id)=1  and id > 12

0 records

Hi,
 
I am bit confused about the order of execution within qualify clause when analytical function is used along with a filter on direct column as shown above. It looks like filter over analytical function is applied first and then the filter over column.
I looked into the manual page but I dont think such example is mentioned hence can some one confirm on this behaviour.
 
Thanks
Zulfi
 
 

Tags:
dnoeth 4628 posts Joined 11/04
08 Jun 2016

It's probably hidden somewhere in the manuals :)
OLAP-functions are calculated after GROUP BY/HAVING and QUALIFY is applied after OLAP, it's similar to FROM->WHERE, GROUP BY->HAVING.
Itzik Ben-Gan wrote some nice articles (for SQL Server: http://sqlmag.com/sql-server/logical-query-processing-what-it-and-what-it-means-you) of course Teradata's proprietary QUALIFY is missing.

Dieter

ToddAWalter 316 posts Joined 10/11
08 Jun 2016

The order of evaluation within the Qualify clause is not important because all conditions in qualify are executed after all of the ordered analytics have been calculated. 
 
If if what is desired is select the rows with ID>12, rownumber the remaining rows then qualify only the first then the ID>12 condition should be placed in the where clause to say that. 

You must sign in to leave a comment.