Oracle outer join by (+) to Postgres conversion

Question

I migrated from the Oracle database to Postgres, but some SQL scripts crashed. I investigated the errors and I found out that Postgres doesn't support outer joins using by (+) convention.

I tried to overwrite this Oracle statement:

SELECT
       r1.fibre AS root_edge_sid,
       sp.sid AS node_sid,
       sp.fid AS node_fid,
       sp.ftid AS node_ftid,
       f.sid AS edge_sid,
       f.fid AS edge_fid,
       f.ftid AS edge_ftid
FROM (SELECT r.FIBRE AS fibre, r.SPLICE_POINT AS splice_point FROM TABLE_A r) r1,
     (SELECT sf.SID AS sid, sf.FID AS fid, sf.FTID AS ftid FROM TABLE_B sf) sp,
     (SELECT r.FIBRE AS fibre, r.SPLICE_POINT AS splice_point FROM TABLE_A r) r2,
     (SELECT f.SID AS sid, f.FID AS fid, f.FTID AS ftid FROM TABLE_C f) f
WHERE r1.splice_point = sp.sid
  AND sp.sid = r2.splice_point(+)
  AND r2.fibre = f.sid(+)
  AND r1.fibre <> f.sid
  AND r1.fibre = 123456;

to Postgres statement:

SELECT r1.fibre AS root_edge_sid,
       sp.sid   AS node_sid,
       sp.fid   AS node_fid,
       sp.ftid  AS node_ftid,
       f.sid    AS edge_sid,
       f.fid    AS edge_fid,
       f.ftid   AS edge_ftid
FROM (SELECT r.FIBRE AS fibre, r.SPLICE_POINT AS splice_point FROM TABLE_A r) r1,
     (SELECT sf.SID AS sid, sf.FID  AS fid, sf.FTID AS ftid FROM TABLE_B sf) sp,
     (SELECT r.FIBRE AS fibre, r.SPLICE_POINT AS splice_point FROM TABLE_A r) r2,
     (SELECT f.SID  AS sid, f.FID  AS fid, f.FTID AS ftid FROM TABLE_C f) f
left outer join r2
on sp.sid = r2.splice_point
left outer join f
on r2.fibre = f.sid
WHERE r1.splice_point = sp.sid
  AND r1.fibre <> f.sid
  AND r1.fibre = 123456;

according to Postgres Wiki but in console I get this error: relation "r2" does not exist

Is possible to make joins between sub-select statements? Or is in the script any error?

You can't use an alias as a FROM table. And it's not clear what you think that is supposed to do. Moreover comma is cross join with lower precedence than keyword joins, so the 1st left join is done 1st, and a table alias from before f among the commas isn't known in that left join. As usual it is important when code doesn't work that you construct a minimal reproducible example including what you expect & why you expected it, referencing authoritative documentation, or we can't point out your particular misconceptions & can only (re)write documentation hoping you would not misinterpret this time. — philipxy
– philipxy, Commented Nov 9, 2020 at 23:46

Sir Montes · Accepted Answer · 2020-11-09 15:37:30Z

1

All those derived tables are useless (even in Oracle).

As far as I can tell, this should be equivalent:

SELECT r1.fibre AS root_edge_sid,
       sp.sid   AS node_sid,
       sp.fid   AS node_fid,
       sp.ftid  AS node_ftid,
       f.sid    AS edge_sid,
       f.fid    AS edge_fid,
       f.ftid   AS edge_ftid
FROM table_a r1
  JOIN table_b sp ON r1.splice_point = sp.sid
  LEFT JOIN table_a r2 ON sp.sid = r2.splice_point
  LEFT JOIN table_c f ON r2.fibre = f.sid
WHERE r1.fibre = 123456
AND r1.fibre <> f.sid;

I think the condition r1.fibre <> f.sid in the WHERE clause was wrong as it turned the outer join back into an inner join.

edited Nov 9, 2020 at 15:37

Sir Montes

1212 silver badges6 bronze badges

answered Nov 9, 2020 at 14:43

user1822

After cosmetic changes is your script working perfectly! I have to move r1.fibre <> f.sid condition to WHERE clause due to returning of duplicated row with NULL values in edge_sid, edge_fid and edge_ftidcolumns.Thanks!

Sir Montes
– Sir Montes

2020-11-09 15:33:00 +00:00
Commented Nov 9, 2020 at 15:33
well if you move the condition AND r1.fibre <> f.sid into the where clause, the outer join on table_c is turned back into an inner join, so you can replace left join table_c with join table_c which then turns the outer join on table_a r2 into an inner join

user1822
– user1822

2020-11-09 15:38:38 +00:00
Commented Nov 9, 2020 at 15:38
"useless" is a poor characterization, since the code is meaningless & not saying so is misleading. (See my comment on the question.)

philipxy
– philipxy

2020-11-09 23:44:54 +00:00
Commented Nov 9, 2020 at 23:44

Add a comment |

Stack Exchange Network

Oracle outer join by (+) to Postgres conversion

1 Answer 1

Your Answer

Hot Network Questions

Oracle outer join by (+) to Postgres conversion

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions