jdgaub jdgaub - 3 months ago 16
SQL Question

Oracle LEFT OUTER JOIN on 3+ tables - (+) Syntax versus ANSI Syntax

Scenario: I need to convert an existing query using (+) outer join syntax to ANSI syntax.
Reason: One of the JOINS requires an OR operand, which is not allowed with the (+) operator, but is allowed with LEFT OUTER JOIN. (At least I'm assuming this is correct.)

Goal of the Query: Table D contains that name of two hierarchies, BB and Commercial. Simple joins through these tables will return 19 Positions and their respective hierarchy. I need to see ALL positions with the appropriate hierarchy if valid, else a NULL value.

(+) Syntax Query - working correctly:

select a.userid, a.firstname, a.lastname, b.name PositionName, d.name Hierarchy

from cs_participant a, cs_position b, cs_positionrelation c, cs_positionrelationtype d

where a.payeeseq = b.payeeseq
and b.ruleelementownerseq = c.childpositionseq(+)
and c.positionrelationtypeseq = d.datatypeseq(+)
and b.removedate = to_date('01/01/2200','dd/mm/yyyy')
and b.effectiveenddate = to_date('01/01/2200','dd/mm/yyyy')
and c.removedate(+) = to_date('01/01/2200','dd/mm/yyyy')
and d.removedate(+) = to_date('01/01/2200','dd/mm/yyyy')
and a.removedate = to_date('01/01/2200','dd/mm/yyyy')
and a.effectiveenddate = to_date('01/01/2200','dd/mm/yyyy')


Result Example:

enter image description here

My Attempt with ANSI Syntax:

select a.firstname, a.lastname, b.name, d.name as "Hierarchy"
from cs_participant a, cs_position b

left outer join cs_positionrelation c on c.parentpositionseq = b.ruleelementownerseq
or c.childpositionseq = b.ruleelementownerseq (--This is the OR clause
that I cannot execute in the (+) syntax query)

left outer join cs_positionrelationtype d on d.datatypeseq = c.positionrelationtypeseq

where a.payeeseq = b.payeeseq
and b.removedate = to_date('01/01/2200','dd/mm/yyyy')
and b.effectiveenddate = to_date('01/01/2200','dd/mm/yyyy')
and a.removedate = to_date('01/01/2200','dd/mm/yyyy')
and c.removedate = to_date('01/01/2200','dd/mm/yyyy')
and c.effectiveenddate = to_date('01/01/2200','dd/mm/yyyy')
and d.removedate = to_date('01/01/2200','dd/mm/yyyy')


ANSI Query Results:

This query returns ONLY the Positions that are assigned to a hierarchy. I need to see ALL positions, with or without a hierarchy assignment, which are currently being excluded from the results.

Answer

You have two date literals as outer join conditions in your first query, but you leave them in the where clause in the second query. To change the syntax properly, those criteria need to be left as part of the join criteria. It's also bad form to combine the two join syntax (i.e. having comma seperated tables and the join keyword in the same query).

Below is the first query properly adapted to SQL-99 syntax:

SELECT a.userid,
       a.firstname,
       a.lastname,
       b.name AS positionname,
       d.name AS hierarchy
FROM   cs_participant a
       JOIN cs_position b ON a.payeeseq = b.payeeseq
       LEFT JOIN cs_positionrelation c
          ON     b.ruleelementownerseq = c.childpositionseq
             AND c.removedate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
       LEFT JOIN cs_positionrelationtype d
          ON     c.positionrelationtypeseq = d.datatypeseq
             AND d.removedate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
WHERE      b.removedate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
       AND b.effectiveenddate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
       AND a.removedate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
       AND a.effectiveenddate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')

Once that's done, adapting it to join on either column is trivial:

SELECT a.userid,
       a.firstname,
       a.lastname,
       b.name AS positionname,
       d.name AS hierarchy
FROM   cs_participant a
       JOIN cs_position b ON a.payeeseq = b.payeeseq
       LEFT JOIN cs_positionrelation c
          ON     (   c.parentpositionseq = b.ruleelementownerseq
                  OR c.childpositionseq = b.ruleelementownerseq)
             AND c.removedate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
       LEFT JOIN cs_positionrelationtype d
          ON     c.positionrelationtypeseq = d.datatypeseq
             AND d.removedate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
WHERE      b.removedate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
       AND b.effectiveenddate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
       AND a.removedate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
       AND a.effectiveenddate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')