In a transform with a reference table how do you denote an outer join?
Jim
Outer Joins....
Moderators: chulett, rschirm, roy
Re: Outer Joins....
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??JDionne wrote:In a transform with a reference table how do you denote an outer join?
Jim
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.