从ANSI转换为Oracle Join语法 [英] Conversion from ANSI to Oracle Join Syntax

问题描述

我想转换以下查询:

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

等同于Oracle连接语法.我的第一次尝试:

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(+)

由于"ORA-01417:一个表最多可能是外部联接到另一个表"的错误而无法工作.

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

我意识到Oracle建议使用ANSI方法，但是我受到以下Oracle错误的困扰:

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/

预先感谢， 本

推荐答案

您将(+)放在错误的一侧，应该是:

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

顺便说一句，我想您知道这是旧 Oracle语法吗? Oracle长期以来一直支持ANSI联接.

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

这篇关于从ANSI转换为Oracle Join语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！