DSXchange

In a transform with a reference table how do you denote an outer join?
Jim

JDionne wrote:In a transform with a reference table how do you denote an outer join?
Jim

Hmm this is an interesting question...I realy didnt stop to think to hard about it till after the post was up. Outer joins arent good for an etl tool im thinking now, it will let through the execptions. Maybe a reject file??
Jim

They are outer joins natively (equi-joins). You have a primary stream of data, everything else is a reference lookup. The only way to impede the flow of a row out a transformer link is to put a contraint in.

If you do a reference lookup, and don't get a value, all columns are @NULL. So, put a NOT(ISNULL(reference.primarykey)) constraint on any of the columns in the primary key of the reference lookup (can't be null because primary keys cannot be null) to determine if the reference is missing. Checking attribute columns is iffy because they legitimately can be null.

You can put anything in a constraint to determine if the row proceeds. If you want an exception file, then put an output link and constrain it accordingly.

Another way to detect whether the lookup failed is to interrogate the link variable NOTFOUND for the reference input link.
Thus, for example, the constraint expression on the output link that handles those rows for which the reference lookup (on input link RefInput) would be simply RefInput.NOTFOUND, which can be found under Link Variables in the operand menu of the Expression Editor.