Join / Lookup Stage Reject Link

dhletl · Post by **dhletl** » Wed Oct 13, 2004 3:41 am

Hi,

Regarding the Lookup or Join Stage -

Consider the scenario where records in driver file DO NOT find a match (reject) based on the Key in the lookup/ reference file.
Then the fields which the lookup/ Join was supposed to pick up from the lookup file / reference file would be populated as blank or null (?) in the corresponding lookup/ join link.

Can each of these stages behave differently across different runs (for same job) - meaning for no match - can it have blank at one time ("") and NULL at other times in the fields being picked up from lookup file.
If so, how can this be resolved?

Essentially, my aim is if my matched and unmatched records in lookup or join stage are allowed to go in the same link how can I logically differentiate them?

Thanks,
Nitin

sandy · Post by **sandy** » Wed Oct 13, 2004 4:03 am

Hi Nitin,

Is NULL or blank a valid value in the lookup file columns which are being outputted?

dhletl · Post by **dhletl** » Wed Oct 13, 2004 4:07 am

yes - am making sure that the output fields are allowed to take NULL values so that passing NULL values should not be a problem.

sandy · Post by **sandy** » Wed Oct 13, 2004 4:22 am

Well I think u got my question wrong nitin. What I wanted to ask was is NULL or blank a valid value in the file which u are using as a reference file for the lookup? If not, then you can easily differentiate in the output records the records for which the lookup was not successful, by looking at these column's value.

Hope I am clear enough this time round

chulett · Post by **chulett** » Wed Oct 13, 2004 6:31 am

If it works anything like a Server job lookup, then you have two options:

Don't check the data fields in the lookup, check one of the key fields for NULL. If you put a value in the key field and it comes back NULL, then you know the lookup failed.

Is there a 'status' you can check? In Server jobs you can check LookupLink.NOTFOUND for True/False to indicate Failure/Success of the lookup.

jayankurianvarghese · Post by **jayankurianvarghese** » Wed Oct 13, 2004 7:07 am

my aim is if my matched and unmatched records in lookup or join stage are allowed to go in the same link how can I logically differentiate them?

What is the meaning of this? How you can pass the the matched and un matched through the same link.

One thing you can try is do the sourting before the join stage. You can set it as ascending or decending.

Try it.

dsxdev · Post by **dsxdev** » Wed Oct 13, 2004 9:15 am

Hi
When you have a join or lookup with only one output link ( matched and unmatched records come in same link) you can identify these records by fetching a not null field from the reference link. If you have any such column, then it should be made nullable yes on the output link.

Then on the output side you will get a null in this field. If this field is not null on the output side also then you will get "" for varchar fields for non matching records and 0 for numeric fields non matching.

It is better if you make it nullable yes so that you can differentiate these default values and nulls and handle the records properly.

Regards

jayankurianvarghese · Wed Oct 13, 2004 10:39 pm

Now i understood your problem. IF you are using the any of the join or Look up stage if it is matched you will get the output cloumns properly else that field should should contain null values. You can even handle this. use the null handle property and one another option is also there, You can set a default value for this. Like that you can handle the null values. Try it out with different data types. What ever it may be you will get atleast quotes.

DSXchange

Join / Lookup Stage Reject Link

Join / Lookup Stage Reject Link

Re: Join / Lookup Stage Reject Link