right outer join

tostay2003 · Post by **tostay2003** » Sat Apr 15, 2006 3:31 pm

Hi all,

Does anyone have idea to implement right outer join in datastage.

for instance,

A.Value=B.Value(+)

Where B is the look up.

Regards

ray.wurlod · Post by **ray.wurlod** » Sat Apr 15, 2006 3:43 pm

It is possible to prove that A RIGHT OUTER JOIN B ON condition is equivalent to B LEFT OUTER JOIN A ON condition for an equijoin, though I don't propose to provide that proof here.

Stream input from table B and perform regular lookups against table A (or a hashed file populated from table A). Do not constrain the output of the Transformer stage and there is your outer join. Voila!

kumar_s · Post by **kumar_s** » Tue Apr 18, 2006 8:58 am

The same could also be realised using merge stage.

kcbland · Post by **kcbland** » Tue Apr 18, 2006 9:02 am

All hashed, ODBC, and OCI lookups to the primary input stream are equi-joins, think of it like a cursor lookup. A found row is not required.