Hint: Avoid Outer Join with all Bind Variables

When constructing custom SQL statements, include at least one join condition that links the two outer joined tables.

Do not use bind variables for all conditions on the outer joined table. If none of the join conditions directly link the two tables, the SQL will not return a row when there are no qualified records in the outer joined table, effectively negating the ‘outer’ join.

Example

This SQL statement uses bind variables and does not include a condition directly relating the two outer joined tables:

select a.emplid, a.birthcountry, b.emplid\
from ps_person a, ps_person b
where a.emplid = :1
and b.emplid (+) = :1
and b.birthcountry (+) = :2;

When the bind variables are substituted:

select a.emplid, a.birthcountry, b.emplid
from ps_person a, ps_person b
where a.emplid = '050305'
and b.emplid (+) = '050305'
and b.birthcountry (+) = 'AAA';

The SQL returns no rows. There are no rows in the ps_person table with birthcountry = AAA, but a row should be returned because this is an outer join.

Instead, use this very similar alternative:

select a.emplid, a.birthcountry, b.emplid
from ps_person a, ps_person b
where a.emplid = :1
and b.emplid (+) = <strong>a.emplid</strong>
and b.birthcountry (+) = :2;

When bind variables are substituted:

select a.emplid, a.birthcountry, b.emplid
from ps_person a, ps_person b
where a.emplid = '050305'
and b.emplid (+) = a.emplid
and b.birthcountry (+) = 'AAA';

The SQL will return the following results:

Scope

This hint applies to COBOL Stored SQL Statements, PeopleTools SQL Objects, PeopleCode SQLEXEC function calls, or anywhere else a SQL may be executed with bind variable substitution.