Teradata - Oracle - Query Rewrite - Qualify & Rank

UDA

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.
Tags (3)
4 REPLIES
Teradata Employee

Re: Teradata - Oracle - Query Rewrite - Qualify & Rank

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

Re: Teradata - Oracle - Query Rewrite - Qualify & Rank

Hi,

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

HTH!

Regards,

MAC
Senior Apprentice

Re: Teradata - Oracle - Query Rewrite - Qualify & Rank

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
Teradata Employee

Re: Teradata - Oracle - Query Rewrite - Qualify & Rank

okay .... my bad! :)