All Forums Analytics
praveenjanagam 5 posts Joined 02/10
03 Feb 2010
Alternate for 'Qualify ' analytical function

Hi All, Qualify function is not working in my teradata system .I am getting error while executing the following query . It is not recognizing the 'qualify' key word. so is there any alternative to "qualify" so that i can execute the query ?SELECT'Total ' as Row_One, Name, Sum (Column2) Column2_Sum, Sum (Column3) Column3_Sum, Sum (Column4) Column4_Sum, (Column2_Sum + Column3_Sum + Column4_Sum) Total_Sum, Rank (Total_Sum) Total_Sum_RankFROM [table]GROUP BY NameORDER BY Total_Sum DESCQualify Total_Sum_Rank <= 10thanks in advance.

dnoeth 4628 posts Joined 11/04
03 Feb 2010

Why don't you post the actual error?"is not working" is not very meaningful, but "5478 Aggregates are allowed only with WindowFunctions."You can't mix old-style OLAP functions with aggregates, so simply rewrite it using ANSI-RANK: RANK () OVER (ORDER BY Total_Sum DESC) Total_Sum_RankDieter

Dieter

praveenjanagam 5 posts Joined 02/10
04 Feb 2010

Thanks For Your quick reply.The exact error message is " 3706 : Synatax error : Expected something between 'QUALIFY' and ';'.And our DBA said that QUALIFY is not going to work in our teradata Systems because of some issues.couuld you suggest alternative to QUALIFY keyword.Thank You..

dnoeth 4628 posts Joined 11/04
04 Feb 2010

"The exact error message is " 3706 : Synatax error : Expected something between 'QUALIFY' and ';'.And our DBA said that QUALIFY is not going to work in our teradata Systems because of some issues."Each TD release since V2R3 supports QUALIFY (the new version of RANK i posted is supported since V2R4).So your error message looks like there's some query text chopped of, this might be caused by your query tool or a buggy ODBC etc. driver.How do you try to run that query and which release you're on?Of course you can rewrite it using the same technique used in other DBMSes without QUALIFY: Derived Tablesselect *from (SELECT'Total ' as Row_One, Name, Sum (Column2) Column2_Sum, Sum (Column3) Column3_Sum, Sum (Column4) Column4_Sum, (Column2_Sum + Column3_Sum + Column4_Sum) Total_Sum, RANK () OVER (ORDER BY Total_Sum DESC) Total_Sum_RankFROM [table]GROUP BY Name) as dtwhere Total_Sum_Rank <= 10ORDER BY Total_Sum DESCDieter

Dieter

You must sign in to leave a comment.