Please convert query oracle to teradata

General

Please convert query oracle to teradata

Please convert following query oracle to teradata

select address.address_id,address.state,dim_tab.src_system,dim_tab.dim_last_mdfd_dt

from address,dim_tab where

dim_tab.src_system(+)='rama' and

address.address_id=dim_tab.ods_addr_id(+) and

address.state=dim_tab.ST(+)

appreciate for your quick responce.

Thanks

vamsi

5 REPLIES
Junior Contributor

Re: Please convert query oracle to teradata

Hi Vamsi,

The (+) indicates the inner table which is filled with NULLs. This should be the same in ANSI syntax:

select address.address_id,address.state,dim_tab.src_system,di m_tab.dim_last_mdfd_dt
from address LEFT JOIN dim_tab
ON dim_tab.src_system='rama' and
address.address_id=dim_tab.ods_addr_id and
address.state=dim_tab.ST

Be careful, more complex queries might be hard to translate. because there are different rules for Oracle and ANSI syntax.

Dieter

Re: Please convert query oracle to teradata

Hi Dieter,

Thanks for your responce but i have one doubt.

dim_tab.src_system(+)='rama' here its right join why you took as a left join, i didnt get it can you please aloborate.

Junior Contributor

Re: Please convert query oracle to teradata

Hi Vamsi,

LEFT and RIGHT in ANSI syntax refer to the position of the table name in the JOIN.

"a left b" is the same as "b right a", in both cases a is the outer table and b is the inner table (joined or padded with NULLs).

And this is (+) in Oracle syntax.

Dieter

Re: Please convert query oracle to teradata

Teradata have package, how to create package concept here.

Enthusiast

Re: Please convert query oracle to teradata

No Teradata doesn't have packages like Oracle does...

Teradata UDFs have packages concept but that is also different from Oracle system...