All Forums UDA
prlmurali 1 post Joined 03/10
24 Mar 2010
Teradata - Oracle - Query Rewrite - Qualify & Rank

Hi,
I am working on Teradata to Oracle migration project.

Select frm_crncycd,to_crncycd,exch_rate from exchange_rate
group by 1,2
qualify(rank(frm_dt)) =1

Can someone explain what is the behavior of above query.
So that i can rewrite the query in Oracle.

ChinaGuy 41 posts Joined 09/09
24 Mar 2010

Qualify ( ) allows filtering and basically behaves like a having clause with order descending in its default syntax. Assuming frm_dt is a date, looks like your query is simply getting the latest exchange rate per from/to exchange currency combination.

Adeel Chaudhry 773 posts Joined 04/08
03 Apr 2010

Hi,

You don't have to do anything to above query .... as QUALIFY & RANK both work fine on Teradata.

HTH!

Regards,

MAC

-- If you are stuck at something .... consider it an opportunity to think anew.

dnoeth 4628 posts Joined 11/04
04 Apr 2010

Hi Adeel,
oif course you're right, it's working on Teradata.
But the original poster wanted to port it to Oracle :-)

Select frm_crncycd,to_crncycd,exch_rate
from
(Select frm_crncycd,to_crncycd,exch_rate,
rank() over (partition by frm_crncycd,to_crncycd order by frm_dt desc) as rnk
from exchange_rate
) dt
where rnk = 1

Dieter

Dieter

Adeel Chaudhry 773 posts Joined 04/08
05 Apr 2010

okay .... my bad! :)

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.