What is the equivalent of oracle (+) = in Teradata in SQL

Database
Teradata Employee

What is the equivalent of oracle (+) = in Teradata in SQL

Hi all,

There is the following query in Oracle fetching 11 rows. In Teradata this sql gives error with the message ' SELECT Failed: 3706: Syntax error: expected something between '+' and ')'.

Anyone could share how this query could be translated into Teradata syntax please. I searched and tried some but not successful. Will appreciate your help.

SELECT DISTINCT C.CLASS2ID , C.DESCRIPTION
FROM CLASS2 C, PRODUCT P, PRODUCT_LOCATION PDL
WHERE
P.CLASS1ID = C.CLASS1ID
AND C.CLASS1ID = '00009'
AND PDL.LOCATIONTYPE (+) = 'S00002'

AND PDL.LOCATIONID (+) = 'S00002'
AND PDL.PRODUCTNUMBER (+) = P.PRODUCTNUMBER
AND NVL(PDL.STATUS(+), P.STATUS) = 2
ORDER BY C.Description

thanks
saj
4 REPLIES
Junior Supporter

Re: What is the equivalent of oracle (+) = in Teradata in SQL

RIGHT OUTER JOIN

But the query doesn't seem to make much sense, (even in Oracle)...

Cheers.

Carlos.
Teradata Employee

Re: What is the equivalent of oracle (+) = in Teradata in SQL

This query is being used in the application by the client for the past about 10 yrs. Some other similar queries are there as well.
Now we are doing conversion from Oracle to Teradata and when testing the application connected to Teradata, at one place where this sql was called failed with the message.

-saj
Junior Supporter

Re: What is the equivalent of oracle (+) = in Teradata in SQL

(+) = is proprietary syntax for outer joins.

You must rewrite those queries with ANSI SQL syntax.

Cheers.

Carlos.
Enthusiast

Re: What is the equivalent of oracle (+) = in Teradata in SQL

SELECT DISTINCT C.CLASS2ID , C.DESCRIPTION
FROM CLASS2 C
join PRODUCT P on P.CLASS1ID = C.CLASS1ID
left join PRODUCT_LOCATION PDL on PDL.LOCATIONTYPE = 'S00002'
AND PDL.LOCATIONID = 'S00002'
AND PDL.PRODUCTNUMBER = P.PRODUCTNUMBER
AND coalesce (PDL.STATUS, P.STATUS) = 2
WHERE C.CLASS1ID = '00009'
ORDER BY C.Description