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
Lookup Key Expression
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 209
- Joined: Fri Jan 09, 2004 1:14 pm
- Location: Toronto, Canada
- Contact:
Re: Lookup Key Expression
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.
Rob W.
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 Wierdsma
Toronto, Canada
bartonbishop.com
Toronto, Canada
bartonbishop.com
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]
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]
Bill
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.