Alternate for 'Qualify ' analytical function

Analytics

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_Rank
FROM [table]
GROUP BY Name
ORDER BY Total_Sum DESC
Qualify Total_Sum_Rank <= 10

thanks in advance.
3 REPLIES
N/A

Re: Alternate for 'Qualify ' analytical function

Why don't you post the actual error?
"is not working" is not very meaningful, but "5478 Aggregates are allowed only with Window
Functions."

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_Rank

Dieter

Re: Alternate for 'Qualify ' analytical function

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..
N/A

Re: Alternate for 'Qualify ' analytical function

"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 Tables

select *
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_Rank
FROM [table]
GROUP BY Name
) as dt
where Total_Sum_Rank <= 10
ORDER BY Total_Sum DESC

Dieter