lookup Failed !!

vijay.barani · Post by **vijay.barani** » Tue Jul 21, 2009 4:28 am

Dear All,
I have a job in which,there are source DRS,3 transformer Stages,Target DRS stage.Also there are lookup stages for all of the transformer stages.
Now,For my second transformer stage,there is a lookup apart from other 5 lookups.The data comes from alll the lookup stages except from that single lookup stage(I used Hash file,Which gets loaded with data in another job).

What could be the reason behind not fetching the data from this lookup ?
I tried the following.
1)CHecked whether the conditions are satisfyng in the database.
2)Tried clearing the hash file(Through admin,'CLEAR.FILE XXX'),then again loading the hash file and then running the main job.

May I have some help in this regard,Please

ArndW · Post by **ArndW** » Tue Jul 21, 2009 5:02 am

Debug your job and find exactly 1 value which is being used for a lookup and manually check wether this key exists in the hashed file. Remember, this is case sensitive and space sensitive (i.e. "Hi there" is not identical to "Hi there ").

chulett · Post by **chulett** » Tue Jul 21, 2009 5:35 am

A hashed file lookup fails when the key values don't match exactly and it is typically something like trailing spaces on one side or the other that do people in. As noted, triple-check your values.

JRodriguez · Post by **JRodriguez** » Tue Jul 21, 2009 7:00 am

Check if:

- You have same hashed file name in both jobs
- The key definition is same in both jobs

vijay.barani · Post by **vijay.barani** » Wed Jul 22, 2009 7:36 am

Dear ArndW,
I had debugged my JOb and found that the key exists the Hash job also.

Dear chulett,
I am very sure I have Ltrimmed and Rtrimmed before loading them into hash files.

Dear JRodriguez,
-Have confirmed again that the hashed file names are same in both the jobs
-Also key definition is same.

My issue didn't yet resolve.What if i try with directly an ODBC Stage rather than the hash file.But I guess this might take more execution than the previous one.

chulett · Post by **chulett** » Wed Jul 22, 2009 7:50 am

And I'm very sure your data is not matching or the lookups would be succeeding. Don't assume - check - view data and click inside the fields, both sides.

vijay.barani · Post by **vijay.barani** » Wed Jul 22, 2009 10:33 pm

Hi Chullet,
I am very sure I have been checking the data in hash file job,In which the data is loaded into hash file from DRS.I've LTRIM &RTRIM in source ODBC stage and also checked in hsh file after loading.I have checked the source data in the main JOB alsono spaces before or after the field values.
Apart from that I've checked in the database whether the lookup and source tables which have matching data and i found some data.Unable to crack where this is going wrong !!

chulett · Post by **chulett** » Wed Jul 22, 2009 11:09 pm

There's no other explanation if you've verified the other points already mentioned:

* The metadata is exactly the same, hashed file writer and reader jobs.
* The hashed file name is exactly the same, reader and writer.

Really, all that's left is key data not matching. So... how about some specifics finally. What fields are defined in the hashed file - what types and which are keys, for starters. And post some sample data, first from the DRS stage you found in the key fields of the hashed file and secondly from the source link. Post your Key Expression and also what constraint / code you're using to determine that the lookup has indeed failed.

In other words, all of the gory details that have been left out so far.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Jul 23, 2009 2:23 am

Few questions for you to answer

1.) Did you change the hashed file metadata after its creation ?
2.) What is the datatype (including length) of the hashed file key column ?
3.) What is the datatype (including length) of source column using which you are doing the lookup ?

Copy one value from key column of hashed file and paste it in a sequential file. Use this sequential file to do the lookup and see if it works.

If it doesn't Craig will respond

vijay.barani · Post by **vijay.barani** » Thu Jul 23, 2009 5:14 am

Code: Select all

Hash file into which the data is loaded from a DRS stage

Hash file which is the lookup to the main job


source satge in the main job



transformation...

chulett · Post by **chulett** » Thu Jul 23, 2009 5:19 am

Not quite sure how that is supposed to help.

(it doesn't)

vijay.barani · Post by **vijay.barani** » Thu Jul 23, 2009 5:22 am

I was trying to post images but failed to do so.

chulett · Post by **chulett** » Thu Jul 23, 2009 5:41 am

OK. Those would need to be uploaded to an image hosting site and then you can use the [Img] tags to link to them. Or just explain things in words to us.

vijay.barani · Post by **vijay.barani** » Thu Jul 23, 2009 6:40 am

HI CHullet,
There are two lookup columns[PERSON_ID(KEY,CHAR,15) & SRC_SYS_ID(KEY,CHAR,5)] which have to match with two source columns[EMPLID(CHAR,11) & SRC_SYS_ID(KEY,CHAR,5)].After the existence of values which are same in lookup and source,We need to populate another column[PERSON_SID(INTEGER,10)] from the lookup.

I'VE CHECKED IN DATABASE,and found values for the SQL Query :
select a.person_sid from ps_d_person a,PS_EX_TIME_HDR b
where
a.person_id=Ltrim(Rtrim(b.emplid)) and
a.src_sys_id=b.SRC_SYS_ID

chulett · Post by **chulett** » Thu Jul 23, 2009 7:41 am

Ah... there's part of the problem - you can't trim CHAR fields and you'll have issue with 11 v. 15, so either make them the same size or treat them as varchars in your job and then trim.

The Devil *is* in the details.