Hash File

[ghostraider]

Hello Everybody, I am doing a look up against a flat file. The only column in Hash file is 'Sample' which is obviously the key. On the flat file i have Sample_Number as the key and i also have the Sample column as non key. The key expression is Sample.FlatFile=Sample.HashFile. The thing now is, even though there are records matching there are no hits coming out until i set a constraint which is similar to the key expression. Does that mean there is a problem with my hash file or am i doing something wrong?

Thanks

[chulett]

The key expression is Sample.FlatFile=Sample.HashFile.

That's not literally what's in your Key Expression, is it?

It should just be something like InputLink.Sample_Number as best as I can tell from your posting.

[ghostraider]

Chulett, it is not literally what i have in my key expression but the constraint is like Link1.Sample=Link2.Sample. where Link1 is for Sequential File and Link2 for hash file.

Yeah the key expression is like what you have mentioned.

Thanks

[narasimha]

Remove the constraint and give it a try..

[chulett]

Ok, good. However, other statements you've made are a little confusing as well. Setting a constraint on an outbound link will not effect an inbound's link ability to do a reference lookup.

And a typical lookup check would be using the Link Variable LinkName.NOTFOUND instead of checking the two values. Hard to say precisely from what you wrote, but your constraint may be equivalent and working fine depending on exactly what you are checking.

If you compare the value in the Input link to the value in the Key expression, they will be equal if the lookup succeeded. The reference value will be NULL if it fails, so the equality check would also fail.

So, after all that I'm left wondering what exactly is the problem here we are trying to solve.

[ghostraider]

Narasimha, if i remove the constraint, rows which dont match are also being output to the target

[narasimha]

Can you please confirm if have you linked the key column from the sequential file to the key expression of the Hashed file?

[ghostraider]

Seq File columns: Sample(non-key), Sample_Number(key)
hash File column: Sample(key).

I linked Sample(non-key) from SeqFile for the Key expression.

[chulett]

All that is good. Now you just need a constraint to keep the unmatched records from being output. Typically, that would be:

Code: Select all

Not(ReferenceLinkName.NOTFOUND)

Change the ReferenceLinkName noted above to your real link name.

[sumeet]

Hi,

this makes it an inner join on the ref.Sample=input.sample:

Code: Select all

Not(ReferenceLinkName.NOTFOUND)

If you dont put this constraint it will be left outer join. WHich means that you should be getting matching and non matching recs from the INPUT

Thanks
Sumeet

[ray.wurlod]

A hash file is a tool used for shaping blocks of hash.

A hashed file is a DataStage table particularly well-suited to key-based lookups because it determines the record location by applying a hashing algorithm to the key value.