Analytics

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-03-2010
11:05 AM

02-03-2010
11:05 AM

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.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-03-2010
03:45 PM

02-03-2010
03:45 PM

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

"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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-04-2010
02:31 AM

02-04-2010
02:31 AM

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..

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..

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-04-2010
03:30 AM

02-04-2010
03:30 AM

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

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