CODEWITHSUNDEEP
sqloracle-databasejoin
user140628
user140628

Reputation:

Conversion from ANSI to Oracle Join Syntax

I would like to convert the following query:

SELECT
    request.requestId
FROM
    request
    LEFT OUTER JOIN incident ON incident.requestId = request.requestId
    LEFT OUTER JOIN changeRequest ON changeRequest.requestId = request.requestId

into it's Oracle join syntax equivalent. My first attempt:

SELECT
    request.requestId
FROM
    request,
    incident,
    changeRequest
WHERE
    incident.requestId = request.requestId(+)
    AND changeRequest.requestId = request.requestId(+)

does not work because of the "ORA-01417: a table may be outer joined to at most one other table" error.

I realise that Oracle recommend using the ANSI approach, however I am "suffering" from the following Oracle bug:

http://awads.net/wp/2007/06/14/when-ansi-sql-join-syntax-does-not-work-in-oracle/

Thanks in advance, Ben

Upvotes: 1

Views: 2395

Answers (2)

JimW
JimW

Reputation: 1

FYI, in our painful experience, complex ANSI inner joins in Oracle (versions 10,11, and 12) occasionally cause ORA-00600 errors (core dumps). We have been forced to backtrack many of our ANSI joins back to Oracle joins to avoid this.

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132710

You have the (+) on the wrong side, it should be:

SELECT
    request.requestId
FROM
    request,
    incident,
    changeRequest
WHERE
    incident.requestId (+)= request.requestId
    AND changeRequest.requestId (+)= request.requestId

BTW I assume you realse this is the old Oracle syntax? Oracle has supported ANSI joins for a long time now.

Upvotes: 7

Related Questions