Is there an automatic way to convert Oracle's join syntax (+) to standard join syntax?

Question

Converting from Oracle to PostgreSQL, have lots of SQL to convert from Oracle's join syntax "(+)" to standard join syntax. Is there an automatic way - tool - to do this? Barring that, are there any rules of thumb to use when doing such a conversion?

For example, convert this:

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

to this

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

You seem to understand the rules. My recommendation is to update the joins in Oracle, test them, and then move the code to Postgres. — Gordon Linoff, Sep 10 '19 at 18:20
Asking for resources outside SO is off-topic. (Good luck finding a clear & complete description of (+) semantics in Oracle documentation, publications, blogs, etc, including the manual re added 12c functionality, or anyone's justified guesses anywhere else. If you succeed let me know.) — philipxy, Sep 10 '19 at 19:42
Nevertheless--This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. (Eg [Oracle (Old?) Joins - A tool/script for conversion?](https://stackoverflow.com/q/2425960/3404097)) — philipxy, Sep 10 '19 at 19:55
A little rule of thumb I've used figuring the old join style join was/is: The plus (+) goes on the side you allow to be data deficient" I would post and explain a couple examples but as a comment would be nasty and apparently your asking how to do something you don't fully understand is sometimes out-of-scope. — Belayer, Sep 12 '19 at 04:36

score 1 · Answer 1 · answered Sep 10 '19 at 22:20

1

Here is an algorithm:

If you have x.a = y.b(+) in the WHERE clause, turn
```
FROM x, y
```
to
```
FROM x LEFT JOIN y ON x.a = y.b
```
similar with th e(+) on the left side and RIGHT JOIN.

answered Sep 10 '19 at 22:20

Laurenz Albe

209,280
17
206
263

That does not even handle the example in the question. It's also unclear about what cases it is meant to apply to. – philipxy Sep 10 '19 at 23:33
My impression is that a Stackoverflow answer should be generally useful. I thought the case was obvious: rewrite an Oracle query with the dreaded `(+)` in it. – Laurenz Albe Sep 11 '19 at 06:10
I don't understand how that comment is a response to mine. I just said you only cover a special case & you're not clear about that & it isn't the case in the example in the question. That has 3 tables joined & a conjunction in the where. Let alone the general case of n tables & arbitrary condition. Maybe you thought I meant that it was not addressing the particular example in the question; I meant it doesn't even apply to the example in the question. – philipxy Sep 11 '19 at 07:02

Is there an automatic way to convert Oracle's join syntax (+) to standard join syntax?

1 Answers1