0

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?

1
  • 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
    Nov 9 '20 at 23:46
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.

3
  • 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
    Nov 9 '20 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 Nov 9 '20 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
    Nov 9 '20 at 23:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.