Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

What is equivalent of oracle (+)= in Teradata

Status
Not open for further replies.

sajisher

Programmer
Dec 3, 2003
45
US
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 ')'.

How this query could be translated into Teradata syntax please. In Oracle (+)= is used for kind of outer join. 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
 
I am not real clear on what you are trying to do and since I am not an Oracle user I cannot fully decipher what your original code is attemping, generally speaking a verbal description would have been easy to work with. That being said hopefully what I provided below will at least give you the framework to finish this off yourself.

[tt]
SELECT
DISTINCT C.CLASS2ID
, C.DESCRIPTION
FROM
CLASS2 C
Inner Join
PRODUCT P
On
P.CLASS1ID = C.CLASS1ID
Left Outer Join
(Select
*
From
PRODUCT_LOCATION
Where
LOCATIONTYPE = 'S00002'
AND
LOCATIONID = 'S00002'
) PDL
On
PDL.PRODUCTNUMBER = P.PRODUCTNUMBER
AND
COALESCE(PDL.STATUS, P.STATUS) = 2
WHERE
C.CLASS1ID = '00009'
AND
ORDER BY
C.Description
[/tt]




Best of luck.


 
bkclaw113, thank you very much. appreciate your help.

saj
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top