从 ANSI 到 Oracle 连接语法的转换

发布于 2024-10-19 10:27:12 字数 816 浏览 3 评论 0原文

我想将以下查询：转换

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

为等效的 Oracle join 语法。我的第一次尝试：

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

由于“ORA-01417：一个表最多可以外部连接到另一个表”错误而不起作用。

我意识到 Oracle 建议使用 ANSI 方法，但是我“遭受”以下 Oracle bug 的困扰：

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

预先感谢，本

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

黯然#的苍凉 2024-10-26 10:27:12

你的 (+) 写错了，应该是：

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

顺便说一句，我假设你意识到这是旧的 Oracle 语法？ Oracle 长期以来一直支持 ANSI 连接。

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.

回复收藏 0 原文