lookup Failed !!
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 78
- Joined: Wed Jun 04, 2008 2:59 am
lookup Failed !!
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
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
Warm Regards,
Vijay
Vijay
-
- Premium Member
- Posts: 425
- Joined: Sat Nov 19, 2005 9:26 am
- Location: New York City
- Contact:
-
- Participant
- Posts: 78
- Joined: Wed Jun 04, 2008 2:59 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.
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.
Warm Regards,
Vijay
Vijay
-
- Participant
- Posts: 78
- Joined: Wed Jun 04, 2008 2:59 am
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 !!
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 !!
Warm Regards,
Vijay
Vijay
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.![Wink :wink:](./images/smilies/icon_wink.gif)
* 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.
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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![Wink :wink:](./images/smilies/icon_wink.gif)
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
![Wink :wink:](./images/smilies/icon_wink.gif)
-
- Participant
- Posts: 78
- Joined: Wed Jun 04, 2008 2:59 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...
Warm Regards,
Vijay
Vijay
-
- Participant
- Posts: 78
- Joined: Wed Jun 04, 2008 2:59 am
-
- Participant
- Posts: 78
- Joined: Wed Jun 04, 2008 2:59 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
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
Warm Regards,
Vijay
Vijay