Lookup output coming as Null

srini.dw · Post by **srini.dw** » Mon Feb 03, 2014 8:13 am

Hi Guys,
Please need your help to fix the lookup issue.

The job design


                                  DataSet 
                                    |
                                    v  
                ODBC connector -> Lookup -> DataSet
                (SQL server)

Lookup Reference is Entire partitioned, and input is hash partioned by a columnname Field1.

The join/lookup key is primary.Field1= reference.col1.

Both the below output columns are of Varchar(100) not null fields. The properties in Lookup Stage, failure is set to continue.

DEPT_LAST_UPDT_DT
DEPT_LAST_UPDTD_BY

I have tried to apply transformer before lookup and have applied TRim function for both input and reference and also peak stage.

The ouput value is coming as Null.

The above values exist in both input and reference.

When I make the Lookup Failure To Reject, iam able to get the 2 values in the reject file.

Any ideas or replies would be great help.

Thanks,

chulett · Post by **chulett** » Mon Feb 03, 2014 8:28 am

The lookup is failing because the values don't match... but then I'm sure you know that. This is going to have to be pretty much all you figuring it out, what difference (however small) is causing the failure. However, clarify for us what are the data types involved on both sides of the lookup. For grins post the two values as well. Again, both sides.

ray.wurlod · Post by **ray.wurlod** » Mon Feb 03, 2014 7:51 pm

Lookup will succeed only if the two values are EXACTLY the same. Yours are not, as proved by them being sent to the reject link. Time for some detective work.

If you post the values from both inputs, we can probably spot the problem (assuming it's not non-printing characters!).

Another remote possibility is that you may have specified an incorrect partitioning algorithm for the reference input link to the Lookup stage.

srini.dw · Post by **srini.dw** » Tue Feb 04, 2014 1:34 am

Thanks for the replies.

The lookup stage is a case sensitive.

Last_Update_Date and Last_UpDate_Date was having letter case difference, hence the records were not flowing to the target.

Thanks,

chulett · Post by **chulett** » Tue Feb 04, 2014 8:13 am

No, any equality check will be 'case sensitive'. If you need it to be case insensitive, apply UPPER or LOWER to both sides. I'm assuming this is resolved and marking it as such.