DSXchange

I have a scenario where I need to lookup a table which has 3 columns
called Begin_Range,End_Range and Acct_Name.

The base table has a column Account. I need to lookup the Xref table and
based on the range it belongs,extract the corresponding Acct_Name.

The lookup has no primary key.

Can anyone suggest a way to do this kind of lookup. The base table is a sequential file and lookup table is an oracle table.

Thanks,
Sri.

Hi,
depending on the volume of data in question, one way is to load into a table your values and then use a select with a join between the 2 tables to form a hash file with the values you have and the relevant data you need from your lookup table.
i.e. load values to a table a, assuming your lookup table is named b.
perform a "select a.value,b.Acct_name from a,b where a.value >= b.Begin_Range and a.value <= b.End_Range" into a hash file and simply perform an exact match between the values in your input stream to the values in your newly generated hash file.

IHTH,

If I understand your problem correctly you want to get the account name where the account number falls between your range begin and range end.

To do this when you setup your ODBC stage, on the columns tab identify the range begin and range end as keys. Then on the general tab change the query type to user defined. Go to the generated SQL and change the equal joins in the where clause to do greater than/less than on the ranges. Now in the transformer link the account to both ranges and this should give you the results that your looking for. I have not used the Oracle stage but I would think it would be similar.

Hi Roy and Keith,

The solution worked like a charm. Thank you. You made the solution look so simple.

Thanks once again ....I appreciate your help.

Sri.