Lookup Key Expression

[Billyqing]

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

[rwierdsm]

Is the lookup a Database lookup or sequential file lookup?

If it's SQL, you could pass your value in to the lookup, create custom sql that has a between clause, i.e.

Code: Select all

select Sal_level 
  from table 
 where :1 between FromSal and EndSal

Rob W.

[Billyqing]

Hi Rob,

Thanks for your response.

The lookup is Hash file. If Key expression can use >= or <= then that should not be a problem.

But you can not use unequl operator for joint the input column with reference column, here are FromSal and ToSal.

I am searching another way to solve this kind of look up. But have not get any idea yet.

Billy[/code]

[ray.wurlod]

Can not be done with a Hashed File stage, since the hashing algorithm uses the primary key, the whole primary key and nothing but the primary key to locate the record.

What you seek to do can be done with a UV stage that refers to the hashed file. Performance won't be all that great without an index on the selection column. You may also need to create a VOC pointer if your hashed file was created in a directory rather than in an account (search the forum for SETFILE).

With a UV stage you can use regular SQL (such as BETWEEN) and you can handle multiple rows being returned if that is a possibility.

[Billyqing]

Hi Ray,

What is the best way I can do?

Is any easy way to do that?

Thanks in advanced

Billy

[ray.wurlod]

The way I described is easy.

You already have the hashed file. Is it in the account? If so you can use a UV stage to refer to it directly. If not, you need to use SETFILE to create a pointer before using the UV stage.

The UV stage treats the hashed file as a regular database table, so you will be able to see - even to adapt - the generated SQL.

Multiple row return is managed in the link properties for the reference input link in the Transformer stage.