All records not loading into hash file

oracledba · Post by **oracledba** » Mon Oct 21, 2013 10:58 am

My Job design is,

Oracle------>Transformer------>HashFile

There are 100 records in source however only 50 records are loading into hash file. The performance statistics show 100 records loaded into hash file however only 50 records are there in the hash file. I checked the log and no warning or errors.

Performance statistics are like this.

ODBC---100 Rows--->Transformer----100 Rows-->Hash

replaced the Hash file with Sequential file and all records are loading no problem.

What is the cause for this???????

I need to use hash file as target because in a follow up job I am looking up this hash file. So I would like all these records to be available for the lookup of the hash file in the transformer coming from the reference link.

-Vetti

chulett · Post by **chulett** » Mon Oct 21, 2013 11:31 am

Hashed files do not allow duplicates and do a 'destructive overwrite' when a duplicate key value is encountered. I suspect this is your issue.

oracledba · Post by **oracledba** » Mon Oct 21, 2013 11:58 am

so if i want all the records to come in is there some property i can enable to make it happen.

MarkB · Post by **MarkB** » Mon Oct 21, 2013 1:01 pm

Pretty simple. As Craig said, don't have duplicate keys.

oracledba · Post by **oracledba** » Mon Oct 21, 2013 1:12 pm

if you want to allow duplicates can you use another stage?

chulett · Post by **chulett** » Mon Oct 21, 2013 1:14 pm

Answer this question first - what do you want to happen when you do a lookup against a key value with duplicates? What should be returned?

oracledba · Post by **oracledba** » Mon Oct 21, 2013 1:31 pm

i want all rows to be returned when duplicates are present during lookup

chulett · Post by **chulett** » Mon Oct 21, 2013 1:52 pm

Then you need to use a stage that allows a 'multi-row result set' which means either the UV or ODBC stages. The former is a bit of a pain to setup and use so I'd suggest the latter set to lookup directly against the (apparently small) source table rather than trying to build an intermediate data set.

Perhaps they've relaxed this 'restriction' and allow that option multi-row on more stages... can anyone confirm / deny?

ray.wurlod · Post by **ray.wurlod** » Mon Oct 21, 2013 3:10 pm

No more server job stage types of which I'm aware.