ray.wurlod wrote:How would I know? You pass the value you want to look up, and link it to the field in which it is likely to occur in the reference data set. ...
Ok.I will try to explain clearly.
Look up primary link is column xyz.
Ref link has col1
col2
Lookup is on xyz---col1
Target field has tarcol1 --col1 (col1will go in this).
lkpval--col2 (col2 goes to this)
I am assuming when the lookup fails , 'lkpval' should have NULL and the value of col2 if the lookup is success.
I can't imagine how it could be valid. "I have something in my right hand and something in my left hand. You can not see them (to you they are unknown, or "null"). Are they the same?"
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
In Lookup stage Primary Link you have column like XYZ and reference link is having col1 and col2. I guess you have done the mapping as below in Lookup stage.
1. Your join/lookup key is primary.XYZ = reference.col1.
2. Your target fields are tarcol1 and lkpval.
3. Your target fields are mapped like reference.col1 To tarcol1 (Target) and reference.col2 To lkpval
4. I am assuming setting the properties in Lookup StageLookup Failure To continue
As per the above code and lookup failure scenario you will get following values:
1. If any lookup failure/Non matching keys then you will get both target fields(tarcol1 and lkpval) are populated with NULL values, since you have mapped tarcol1 field from reference.col1 (Not primary.XYZ).
2. In other way if you map tarcol1 from primary.XYZ (Not reference.col1) then tarcol1 have value and lkpval have populated with NULL.
As you mentioned in the first post, (In my look up stage , in the output I introduced this new field named lkpval which is varchar. I am setting this to a key value from the reference link. But when the lookup fails this field (lkpval) is not set to NULL. It is just set to blank.I want to explicitly check for NULL. As blank can also be a key field. lkpval is set as nullable.)
Datastage should not have any disparity, if u set the target lkpval stetted to NULL. But in your case it is.
Please check my suggestions :
1. Trim the lookup/joining keys before join/lookup if the key field's data type is CHAR or VARCHAR.
2. Check the leading zeros ('0') if any misleading on both sides before join/lookup if the key fields data type is INT or DECIMAL.
3. Check proper partition technique have done (If you are using Lookup stage, Reference link from lookup stage should be an ENTIRE partition technique).
4. Check the Nullability of target fields.
Then have the Transformer stage after immediate of Lookupstage.
In Transformer stage:
1. Create one stagevariable and check the NULL condition of lkpval (your target field on lookupstage) like If IsNull (lkpval) Then "Default Value" Else If Len(Trim(lkpval)) = 0 Then "Default Value" Else lkpval.
2. Here you can identify easily if lookup failure's (If lkpval is having "Default Value" then your Lookup get's failed or it successes.)
3. Map this stagevariable to actual Target field.
hemachandra.m wrote:As I understood your requirement:
As you mentioned in the first post, (In my look up stage , in the output I introduced this new field named lkpval which is varchar. I am setting this to a key value from the reference link. But when the lookup fails this field (lkpval) is not set to NULL. It is just set to blank.I want to explicitly check for NULL. As blank can also be a key field. lkpval is set as nullable.)
Datastage should not have any disparity, if u set the target lkpval stetted to NULL. But in your case it is.
Please check my suggestions :
1. Trim the lookup/joining keys before join/lookup if the key field's data type is CHAR or VARCHAR.
2. Check the leading zeros ('0') if any misleading on both sides before join/lookup if the key fields data type is INT or DECIMAL.
3. Check proper partition technique have done (If you are using Lookup stage, Reference link from lookup stage should be an ENTIRE partition technique).
4. Check the Nullability of target fields.
.
I didn't want to make my key columns mentioned in the reference link nullable.Because I wanted to keep the metadata same for this dataset whcih is created in some other job. But this reference ds did have a column(non-key) which is nullable. Now as per my understanding from your explanations, I mapped lkpval(target column) to this field col2 which is nullable. Now the job is running as expected. For lookup failure, it is passing NULL to lkpval.
As conclusion, if I am mapping a field to lkpval to check for lookup failure or success, the origin field should be NULLABLE.
