Lookup failure for data length mismatch

Nagasudheerkumar · Post by **Nagasudheerkumar** » Mon Apr 20, 2015 1:35 pm

Hi All,

I have a job design and scenario below.

                         Oracle Reference
                                |  
                            Sort Stage
                                |
                           Transformer
                                |
                                |                                  
oracle Source-->Transformer--->Lookup---> Tranformer---> Oracle Target
                                |
                                |
                        Oracle(Reject)

1) Oracle Source(Query joining with 5 tables) contains Title_Ids(lookup column)-- char2(70 char)
2) Oracle Reference Contains Title_ids(lookup column)-- char2(20 char)
3) When i am trying to lookup based on lookup key column(Title_Id) some records from source are getting rejecting into Oracle(Reject) eventhough it has matching records in reference.
4)From both the source and reference i am getting duplicate Title_Ids so I have to take only latest date(registration_date), title_Ids from reference,so i used to sort stage and generated change code key column with desc and got the latest title_ids from Reference.

Oracle Source count(725)
Oracle reference Count(6001471)
After sorting based on date(8803)
Target count (28)
Oracle Reject(703)

I am able to see atleast 200 records which are matching between source and reference but not going into Target.

Below Data will be like this:

Source:char2(70 char) Not Null
Title_Ids
01099393999936473840
01099393999936473840

Reference:char2(20 char) Not Null
Title_Ids Market Value(reference) Registration_date
01099393999936473840(after sorting) 100000 20-Apr-15

Target:char2(70 char) Not Null
Title_Ids Title_Ids Market Value(reference) Registration_date
01099393999936473840 01099393999936473840 100000 20-Apr-15
01099393999936473840 01099393999936473840 100000 20-Apr-15

Could you please let me know what needs to be done for this lookup failure scenario.

Thanks,
Sudheer

chanaka · Post by **chanaka** » Mon Apr 20, 2015 3:11 pm

Can you do a leading and trailing space trim for both source and lookupsource columns before the lookup.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 20, 2015 5:21 pm

Perhaps the char2 columns are padded to length? Because they are different lengths, that could result in mismatches occurring.

Nagasudheerkumar · Post by **Nagasudheerkumar** » Mon Apr 20, 2015 10:27 pm

Thanks for the reply.

I have used TrimLeading and Trailing spaces and i changed the datatype to Varchar2 also and tried, But not working..

Is there anything which i am missing

ray.wurlod · Post by **ray.wurlod** » Mon Apr 20, 2015 10:56 pm

What is the value of APT_STRING_PADCHAR environment variable in your project?

Nagasudheerkumar · Post by **Nagasudheerkumar** » Tue Apr 21, 2015 12:00 am

The value for APT_STRING_PADCHAR is 0x0..

ray.wurlod · Post by **ray.wurlod** » Tue Apr 21, 2015 4:41 pm

Try changing it to 0x20, at least for this job. That changes it from a NUL to a space character, which will be handled by your Trim() functions.

Nagasudheerkumar · Post by **Nagasudheerkumar** » Wed Apr 22, 2015 1:49 am

Hi Ray Thanks for the reply. I have issue in Sorting i am using only date (desc) now i added 2 columns id(asc) as well and issue is sorted.