Page 1 of 1
Can lookup key be a range
Posted: Tue Nov 25, 2003 10:32 am
by yiminghu
Hi All,
I have a question regarding to lookup. In our table, there is a field called visit_date, if the visit_date falls into certain range which is defined in another talbe, that record is treated as valid one otherwise, that is invalid one.
I don't know how to implement this as lookup?
Thanks,
Yiming
Re: Can lookup key be a range
Posted: Tue Nov 25, 2003 10:35 am
by Teej
yiminghu wrote:Hi All,
I have a question regarding to lookup. In our table, there is a field called visit_date, if the visit_date falls into certain range which is defined in another talbe, that record is treated as valid one otherwise, that is invalid one.
I don't know how to implement this as lookup?
Use a constraint.
One possible idea to be able to do that is to create a dummy field to automatically join the two links via a lookup. There, you have the capability to use the constraint to set the range.
I am quite sure others will chime in with other solutions that would perform better.
-T.J.
Posted: Tue Nov 25, 2003 10:41 am
by kcbland
It depends on what type of lookup stage you are going to use:
OCI/ODBC: You have to define the appropriate SQL statement in the stage. You will have to supply the appropriate columns in the key expressions.
Hash file: Not without some work. Unless performance is an issue, this solution is highly customized, but will yield the best results.
UV/ODBC: You will be able to use a hash file, but this SQL interface to the hash file will not be as efficient as the hash file stage. It will allow you to leverage a pre-existing hash file. You will set it up similiar to the OCI/ODBC stage.
Easier Method
Posted: Tue Nov 25, 2003 5:15 pm
by sdevashis
HI,
I am new to Datastage, but I had come across this. A better way of solving your problem is to write a routine taking dates as two arguments and return you the category_key directly. So you dont need to go into the lookup stuff at all. HOWEVER this method will make the stuff hard coded.
Hope this solves this probelm.
![Arrow :arrow:](./images/smilies/icon_arrow.gif)
Posted: Tue Nov 25, 2003 5:20 pm
by ray.wurlod
kcbland wrote:It depends on what type of lookup stage you are going to use:
OCI/ODBC: You have to define the appropriate SQL statement in the stage. You will have to supply the appropriate columns in the key expressions.
Hash file: Not without some work. Unless performance is an issue, this solution is highly customized, but will yield the best results.
UV/ODBC: You will be able to use a hash file, but this SQL interface to the hash file will not be as efficient as the hash file stage. It will allow you to leverage a pre-existing hash file. You will set it up similiar to the OCI/ODBC stage.
![Idea :idea:](./images/smilies/icon_idea.gif)
The OCI/UV/ODBC solutions will benefit greatly from the column(s) on which the BETWEEN constraint is performed being indexed.