Hashfile Lookup

cosec · Post by **cosec** » Sun Jul 22, 2007 9:57 pm

Is it possible to do a lookup on a hashfile to retrieve records between certain dates.....I am able to do what I want using a DB2 stage....but using a hashfile I cannot select the particular records I want......I used the account name option but it disables the selection when I use it as a lookup..... any suggestions ?

hamzaqk · Post by **hamzaqk** » Sun Jul 22, 2007 10:17 pm

use the universe stage if you want to do a range match .. hashfile does an exact match ..

ArndW · Post by **ArndW** » Sun Jul 22, 2007 10:17 pm

Hashed file lookups do not support a BETWEEN type of lookup. You can use the SQL query on a hashed file (ODBC or UniVerse stages), but the performance is going to be abyssmal (full table scan for each row). There are a number of solutions for better performance to this type of problem that have been covered before in this forum.

cosec · Post by **cosec** » Sun Jul 22, 2007 10:21 pm

does that mean I am better off using my filtered DB2 stage as the lookup to do the same function ?

ArndW wrote:Hashed file lookups do not support a BETWEEN type of lookup. You can use the SQL query on a hashed file (ODBC or UniVerse stages), but the performance is going to be abyssmal (full table scan for each ...

ArndW · Post by **ArndW** » Sun Jul 22, 2007 11:04 pm

That is hard to say, as it depends on your implementation. In some cases it is better to create hashed lookup files for this and changing the lookup from a range to a simple unique keyed one [at the cost of making the data volume much larger]