Lookup Key Expression

Billyqing · Post by **Billyqing** » Tue Jun 13, 2006 3:41 am

simple lookup is equal join for Primary input with look up keys.

Is any way to lookup with non-equaljoint expression?

For example:

in primary input:

EmpNo Salary
1001 4550.55

in Reference input
Sal_level FromSal EndSal
1 1000.00 1999.99
2 2000.00 2999.99
3 3000.00 3999.99
4 4000.00 4999.99
............

Output should be:

EmpNo Salary Sal_level
1001 4550.55 4

Anyone knows how to do so?

Thanks in advanced

Billy

chulett · Post by **chulett** » Tue Jun 13, 2006 6:48 am

Only pass along the hashed lookup failures?

DSguru2B · Post by **DSguru2B** » Tue Jun 13, 2006 7:37 am

Isnt this a duplicate post

Use a temp table to load your hashed file into it and use the sql mentioned in your otherpost.

chulett · Post by **chulett** » Tue Jun 13, 2006 8:34 am

Yah, noticed that after I posted here. However, I didn't notice the '4' in the output... and what the OP is looking for is specifically a 'range lookup' rather than a 'non-equaljoint' one.

Ken Bland posted a way to do this in hashed files which I've leveraged in the past. Not for the faint of heart, but does work rather nicely.

Billyqing · Post by **Billyqing** » Tue Jun 13, 2006 8:45 am

Hi Raig,

Yaeh, you can say it is a 'Range Lookup'. I am looking for any way instead of put everything together into DB, then use SQL.

Thanks

chulett · Post by **chulett** » Tue Jun 13, 2006 8:52 am

Ken Bland posted these routines back in 2003 to do ranged lookups in hashed files. Only 'problem' is they are considered Premium Content now, but I'm sure you could contact Ken directly as all of his stuff is 'open source' I do believe.