What is the best way to define a constraint in my situation where I am performing a lookup and want the key column of my source link to match the key column of the lookup link, but i only want data where my non-key column does not match. In psuedocode, this constraint looks like this....
(source.key = lookup.key) and (source.non-key <> lookup.non-key)
Most effective way to define a constraint
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
When the lookup fails, the reference input columns are all set to NULL by the Transformer stage, which invalidates the use of "=" and "<>" operators.
You can test the link variable NOTFOUND for the reference input link, which is reliable if the link is fed by Hashed File stage but not, apparently, for other stage types, or you can use IsNull() explicitly to test the key column from the reference input link.
You can test the link variable NOTFOUND for the reference input link, which is reliable if the link is fed by Hashed File stage but not, apparently, for other stage types, or you can use IsNull() explicitly to test the key column from the reference input link.
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.
But if the nullity of the columns from the reference link is set to 'Not Null' , then those columns would be assigned a value. Varchar - a zero length string , Integer - 0 ..ray.wurlod wrote:When the lookup fails, the reference input columns are all set to NULL by the Transformer stage, which invalidates the use of "=" and "<>" operators.
You can test the link variable NOTFOUND f ...
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: