3

There is a section of code in one of my sqls that goes like this:

FROM 
Table1
LEFT OUTER JOIN
              TABLE2
           ON (Table1.Field1 = Table2.Field1))
    LEFT OUTER JOIN
        TABLE3
       ON(Table2.Field1 = Table3.Field1))
    LEFT OUTER JOIN
        Table4
       ON(Table3.Field1 = Table4.Field1))
    LEFT OUTER JOIN 
        Table5
       ON(Table4.Field1= Table5.Field1)

Table6.Field1(+) = 'Y' (How do I convert this?)

I understand this is a right outer join, what would the syntax be updated to today's modern syntax?

Edit1: Updated to show more of the query. I've converted all the other joins, just missing the last line to be converted.

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
studentofarkad
  • 155
  • 1
  • 1
  • 8
  • 1
    You need to show more of the query. – Gordon Linoff Oct 18 '17 at 16:14
  • You kinda have the answer there using left instead of right...use a 'right outer join' or 'right join' for short. Discussion on Stack of the two formats: https://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89 – Twelfth Oct 18 '17 at 16:25
  • The problem is I'm not sure how the line of code would look like, is it: RIGHT OUTER JOIN Table6.Field1 = 'Y' ? – studentofarkad Oct 18 '17 at 16:27
  • Table6.Field1(+) = 'Y' translates to NVL(table6.field1, 'Y') = 'Y'. – D. Mika Oct 18 '17 at 16:31
  • D. Mika, confused at your explanation. I am not trying to substitute any null values with a 'Y'? – studentofarkad Oct 18 '17 at 20:48
  • Hi. That doesn't parse. Show a working minimal query. That is why questions without an [mcve] are off-topic. – philipxy Oct 20 '17 at 03:17

1 Answers1

1

(+) was used to indicate an outer join in the sense that unmatched rows are also allowed. However the snippet you have shared with us does not contain enough information about "table6"

Based on the progression of joins t1 to t2, t2 to t3, t3 to t4, t4 to t5, my guess would be t5 to t6, like this:

FROM Table1
LEFT OUTER JOIN TABLE2 ON Table1.Field1 = Table2.Field1
LEFT OUTER JOIN TABLE3 ON Table2.Field1 = Table3.Field1
LEFT OUTER JOIN Table4 ON Table3.Field1 = Table4.Field1
LEFT OUTER JOIN Table5 ON Table4.Field1 = Table5.Field1
LEFT OUTER JOIN Table6 ON Table5.Field1 = Table6.Field1
         AND Table6.Field1 = 'Y'

CREATE TABLE Table1    (TBL VARCHAR2(2), FIELD1 VARCHAR2(1));
INSERT INTO Table1 (tbl, field1)          VALUES ('t1', 'y');
INSERT INTO Table1 (tbl, field1)          VALUES ('t1', 'n');
INSERT INTO Table1 (tbl, field1)          VALUES ('t1', 'y');
INSERT INTO Table1 (tbl, field1)          VALUES ('t1', 'n');

CREATE TABLE Table6    (TBL6 VARCHAR2(2), FIELD1 VARCHAR2(1));
INSERT INTO Table6 (tbl6, field1)         VALUES ('t6', 'y');
INSERT INTO Table6 (tbl6, field1)         VALUES ('t6', 'n');
INSERT INTO Table6 (tbl6, field1)         VALUES ('t6', 'y');
INSERT INTO Table6 (tbl6, field1)         VALUES ('t6', 'n');

OLD SYNTAX

select table1.tbl, table1.field1, table6.tbl6, table6.field1 as t6_field1
from table1, table6
where table1.field1 = table6.field1(+)
and table6.field1(+) = 'y';

TBL | FIELD1 | TBL6 | T6_FIELD1
:-- | :----- | :--- | :--------
t1  | y      | t6   | y        
t1  | y      | t6   | y        
t1  | y      | t6   | y        
t1  | y      | t6   | y        
t1  | n      | null | null     
t1  | n      | null | null

NEW SYNTAX

select table1.tbl, table1.field1, table6.tbl6, table6.field1 as t6_field1
from table1
left join table6 on table1.field1 = table6.field1
    and table6.field1 = 'y';

TBL | FIELD1 | TBL6 | T6_FIELD1
:-- | :----- | :--- | :--------
t1  | y      | t6   | y        
t1  | y      | t6   | y        
t1  | y      | t6   | y        
t1  | y      | t6   | y        
t1  | n      | null | null     
t1  | n      | null | null

dbfiddle here

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51