Hashed file lookup problem

rsunny · Post by **rsunny** » Tue Sep 21, 2010 11:03 am

Hi everyone ,

I have an issue regarding the Hash file.i am using hash file as a look up and trying to do some transformations with my source to get the output.everything is executing as it is showing a green line but the link from hash to transformer is showing a green line but with 0 rows.i can able to see the rows coming from a database to hash file but from hash to transformer i don't see any rows but i can able to view the data through my hash file.can anybody suggest me what is the error.is this a bug or is there any previlages as a developer i am lacking.

Thanks in advance.

vivekgadwal · Post by **vivekgadwal** » Tue Sep 21, 2010 11:16 am

It indicates that there were no rows were found during the lookup. Check the key values and trim unnecessary spaces accordingly.

Post by **daignault** » Tue Sep 21, 2010 11:16 am

I'd suggest that you have not properly defined the key. A lookup is nothing more than a join "where table.key = lookup.key"

If you are getting 0 rows from your lookup then your join is not defined correctly. Look at your keys, etc.

Cheers,

Ray D

rsunny · Post by **rsunny** » Tue Sep 21, 2010 11:38 am

Hi everyone ,

i recheked again .i had defined the Keys properly but still getting the same error. The same 0 rows are showing.

Thanks in advance

arunkumarmm · Post by **arunkumarmm** » Tue Sep 21, 2010 12:57 pm

rsunny wrote:Hi everyone ,

i recheked again .i had defined the Keys properly but still getting the same error. The same 0 rows are showing.

Thanks in advance

What are the keys you have defined? Can you post some sample values from your driver and a matching row in the lookup?

Also, Check for spaces in the values, leading zeros or upper/lower cases etc.,

Onemore thing, check whether you are using the same file name in your input and output tabs of your Hashed File.

chulett · Post by **chulett** » Tue Sep 21, 2010 1:03 pm

As noted, the Director shows the number of successful lookups to the hashed file of which you had zero. As also already noted, this is typically a trimming issue, something like "ABC" will not match with "ABC ", for example.

vivekgadwal · Post by **vivekgadwal** » Tue Sep 21, 2010 1:10 pm

rsunny wrote: i recheked again .i had defined the Keys properly but still getting the same error...

As I noted here earlier, this is most likely an issue with some key field having an extra space or something like that. Please check that...

rsunny · Post by **rsunny** » Tue Sep 21, 2010 1:33 pm

Hi everyone,

I have checked my key fields data whether there might be a trimming issue or spaces or might be a null, but i think there is no such issues in my key fields data.i have taken two fields as keys and tried to compare with my source data through the transformer so i can able to see the rows coming from source to transformer but 'o' rows from hash file to transformer.Might be my data might be mismatched with any of the nulls or spaces but i think i recheked my data.i didnt find any issues of spaces or nulls etc.And i dint see any warnings or fatal errors in log director.i tried to remove one field as key and used only one field as key but still getting the same issue, but i will still try to check my data with the fields having keys.

thanks in advance.

creatingfusion · Post by **creatingfusion** » Tue Sep 21, 2010 1:48 pm

if you have checked everything then its strange if you do not get any data from the hash to the look up although you have matching data.

The job as i suppose you are looking at have some source link entering the lookup and there being a database link updaing the hash file that goes into the lookup reference link.

To Confirm the lookup has matching values - remove the hash file stage from yours job and directly have the reference from the database stage. If you are able to get valid output here then there is issues probably with yours output mapping in the hash file stage. Validate the mapping in the output tab of the hash file stage then.

Hope these recomendations helps.
Abhijit

rsunny · Post by **rsunny** » Tue Sep 21, 2010 1:55 pm

I am using a flatfile so in server i have to use a hash file to do look up.

creatingfusion · Post by **creatingfusion** » Tue Sep 21, 2010 2:05 pm

Probably you are doing the lookup in a transformer stage where you have the main link coming from the sequential file and the reference from the hash file stage.

You might be updating the hash file somewhere first from a database stage probably ODBC. so in reference link remove the hash file and directly put the odbc stage output link as reference.

Let me know the version of the datastage installed if you cannot work out this

chulett · Post by **chulett** » Tue Sep 21, 2010 2:12 pm

Check closer. Lookup failures simply mean you did not get an equality match on the values. Period. So, for whatever reason, your values are not matching.

Do a View Data on both the source and lookup source, and click inside the Key fields on both sides. Move your cursor around inside each field. That's the best way I've found to "see" the spaces you have on one side or the other than is causing the mismatch. Assuming text data, of course. If you still can't find it, can you post some examples of the "matching" data from both sides, please?

rsunny · Post by **rsunny** » Tue Sep 21, 2010 2:18 pm

creatingfusion wrote:Probably you are doing the lookup in a transformer stage where you have the main link coming from the sequential file and the reference from the hash file stage.

You might be updating the hash file somewhere first from a database stage probably ODBC. so in reference link remove the hash file and directly put the odbc stage output link as reference.

Let me know the version of the datastage installed if you cannot work out this

For some reasons , i am using the reference link as a sequencial file and the main link also the sequential file.i am using DS7.5.x Server edition.I think it might be a data issue i think , i will try to check my data once again.

Thanks in advance

rsunny · Post by **rsunny** » Tue Sep 21, 2010 2:23 pm

chulett wrote:Check closer. Lookup failures simply mean you did not get an equality match on the values. Period. So, for whatever reason, your values are not matching.

Do a View Data on both the source and lookup source, and click inside the Key fields on both sides. Move your cursor around inside each field. That's the best way I've found to "see" the spaces you have on one side or the other than is causing the mismatch. Assuming text data, of course. If you still can't find it, can you post some examples of the "matching" data from both sides, please?

I am not getting a matched data as there is no data coming from hash file so the data only is coming from the source file and i am comparing the the source data and hash file using the transformer ,so i can able to get all the records which are unmatched because as there is no data coming out from hash file.Anyway i will try to recheck my data in both the source and hash file and see if i could find a solution.

Thanks in advance

shepli · Post by **shepli** » Tue Sep 21, 2010 2:32 pm

Right click on the hashed file and view the values of the output link to see if you can see the values in the hashed file.
If you used a local container, make sure the links at both ends of the container are identical.
Hope this helps.

shepli