Range Lookups V7.5.2

[NigeGriff]

I have a scenario were i need to lookup a date value on the input stream against a reference oracle table.

Each reference table row contains a start and end date value and i need to lookup the row where the input date value falls between the start and end date values on the reference table row.

what is the most efficient range lookup method to meet the above requirement?

[Ragunathan Gunasekaran]

Hi ,

You could use the write Range map stage and write a dataset that partitions your data based on a range. You could then use this Range partitioned dataset with the lookup stage to perform a range look up

[ray.wurlod]

I'm not sure this is correct. Write Range Map stage creates a range map to support Range partitioning, not Range lookup.

Range (or "between") lookups are available in version 8. ABout the only way I know to do it in version 7 is to use a sparse lookup with user-defined SQL containing an appropriate BETWEEN clause.

[NigeGriff]

Hi Ray,
I am trying to implement what you have suggested but was wondering how to reference a column on the input link in the user defined SQL as 0ne of the predicates. i.e.

SELECT RATE_STERLING
from CURRENCY_RATES_DIM
where FINANCIAL_DATE >= CURRENCY_RATES_DIM.START_DATE
and FINANCIAL_DATE <= CURRENCY_RATES_DIM.END_DATE

In the example above, FINANCIAL_DATE is a column on the input link.

[ray.wurlod]

You need a parameter marker or reference to the ORCHESTRATE structure, not the column name. For example one of the following.

Code: Select all

SELECT RATE_STERLING 
from CURRENCY_RATES_DIM 
where ? >= CURRENCY_RATES_DIM.START_DATE 
and ? <= CURRENCY_RATES_DIM.END_DATE 

Code: Select all

SELECT RATE_STERLING 
from CURRENCY_RATES_DIM 
where ORCHESTRATE.FINANCIAL_DATE >= CURRENCY_RATES_DIM.START_DATE 
and ORCHESTRATE.FINANCIAL_DATE <= CURRENCY_RATES_DIM.END_DATE 

[NigeGriff]

Thanks ray the second option worked fine.