Replacing Oracle outer join+ operator

Question

I need to rewrite a few hundreds of existing Oracle SQL queries and some of them use the + operator for outer join. Have I have converted them correctly and what are my errors?

Original code:

SELECT  DISTINCT 
:s1 || '^' || ll.street || '^' || tat.attribute_name    
FROM    
tsec ,
    ll,
    ta,
    tat
WHERE   
    tat.reference       LIKE 'ENGPYMNT%'
    AND tat.system_name     = 'LAND'
    AND ta.attribute_type_id        = tat.attribute_type_id 
    AND ll.legal_id         = ta.source_id
    AND tsec.program(+)     = 'TD_ATTRIBUTE_DETAILS'
    AND tsec.item_name(+)       = 'ATTRIBUTES VIEW'
   AND  tsec.system_name(+)     = 'LAND'
    AND tsec.sql_user(+)        = :SqlUser
    AND tsec.relation_type(+)       = 'ATTRIBUTE'
    AND tsec.relation_id(+)     = tat.attribute_type_id
    AND tsec.sec_level      > 0
    AND LTRIM(TO_CHAR(ll.house, '999999'))  LIKE :s1
    AND ll.street           LIKE UPPER( :s2 )
    AND tat.attribute_name      LIKE :s3;

Rewritten Code:

select DISTINCT
    :s1 || '^' || land_legal.street || '^' || td_attribute_types.attribute_name 
from
    ta join tat on ta.attribute_type_id = tat.attribute_type_id 
join 
    ll on ll.legal_id   = ta.source_id
left join 
    tsec on tsec.relation_id    = tat.attribute_type_id
    AND tsec.program    = 'TD_ATTRIBUTE_DETAILS'
    AND tsec.item_name  = 'ATTRIBUTES VIEW'
    AND tsec.system_name    = 'LAND'
    AND tsec.sql_user   = :SqlUser
    AND tsec.relation_type  = 'ATTRIBUTE'
where
    tsec.sec_level      > 0
    AND tat.reference       LIKE 'ENGPYMNT%'
    AND LTRIM(TO_CHAR(ll.house, '999999'))  LIKE :s1
    AND ll.street   LIKE UPPER( :s2 )
    AND tat.attribute_name LIKE :s3
;

Answer 1

The error is in the original code (rather than in your translation of it):

AND tsec.sec_level      > 0

Does not use the (+) relationship so tsec.sec_level cannot be NULL and this effectively converts the join from an outer to an inner join. Your translation of the code is "correct" but it perpetuates the issue from the original and the LEFT JOIN will not be implemented by the SQL engine as a LEFT JOIN due to the WHERE clause and will actually be an INNER JOIN.

The correct translation from legacy comma joins to the modern ANSI join syntax is:

SELECT DISTINCT 
       :s1 || '^' || ll.street || '^' || tat.attribute_name    
FROM   ta
       INNER JOIN tat
       ON (ta.attribute_type_id = tat.attribute_type_id)
       INNER JOIN ll
       ON (ll.legal_id = ta.source_id)
       INNER JOIN tsec
       ON (tsec.relation_id = tat.attribute_type_id)
WHERE  tat.reference      LIKE 'ENGPYMNT%'
AND    tat.system_name    = 'LAND'
AND    tsec.program       = 'TD_ATTRIBUTE_DETAILS'
AND    tsec.item_name     = 'ATTRIBUTES VIEW'
AND    tsec.system_name   = 'LAND'
AND    tsec.sql_user      = :SqlUser
AND    tsec.relation_type = 'ATTRIBUTE'
AND    tsec.sec_level     > 0
AND    LTRIM(TO_CHAR(ll.house, '999999'))  LIKE :s1
AND    ll.street          LIKE UPPER( :s2 )
AND    tat.attribute_name LIKE :s3;

---

However, if the original code is fixed so the line reads:

AND tsec.sec_level(+)      > 0

Then the correct code would be:

SELECT DISTINCT 
       :s1 || '^' || ll.street || '^' || tat.attribute_name    
FROM   ta
       INNER JOIN tat
       ON (ta.attribute_type_id = tat.attribute_type_id)
       INNER JOIN ll
       ON (ll.legal_id = ta.source_id)
       LEFT OUTER JOIN tsec
       ON (   tsec.relation_id   = tat.attribute_type_id
          AND tsec.program       = 'TD_ATTRIBUTE_DETAILS'
          AND tsec.item_name     = 'ATTRIBUTES VIEW'
          AND tsec.system_name   = 'LAND'
          AND tsec.sql_user      = :SqlUser
          AND tsec.relation_type = 'ATTRIBUTE'
          AND tsec.sec_level     > 0
          )
WHERE  tat.reference      LIKE 'ENGPYMNT%'
AND    tat.system_name    = 'LAND'
AND    LTRIM(TO_CHAR(ll.house, '999999'))  LIKE :s1
AND    ll.street          LIKE UPPER( :s2 )
AND    tat.attribute_name LIKE :s3;