Can lookup key be a range

[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

[Teej]

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.

[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.

[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.

[ray.wurlod]

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.

The OCI/UV/ODBC solutions will benefit greatly from the column(s) on which the BETWEEN constraint is performed being indexed.