Can i use a lookup to get a key field from 2 date ranges

[james garner]

Hi all,

I am trying to get a key field from a reference table based on a date range overlapping another date range in the reference table.

main table:
rec # field_id eff_dt term_dt
1 1001 2003-01-01 2007-12-31

reference table:
rec # field_id eff_dt term_dt
1 1001 2001-01-01 2002-12-31
2 1001 2004-01-01 2005-12-31
3 1001 2001-01-01 2010-12-31
4 1001 2002-01-01 2005-12-31
5 1001 2006-01-01 2010-12-31

so what i am trying to do is get is all reference table records where the main table date range overlaps the reference table date ranges.. in the example above record 1 is dropped and the lookup picks up records 2-5 because they overlap. i would even be happy with the lookup just grabbing the last record (5).

I haven't been able to figure out how to use the range lookup options correctly to do this or if it's even possible in a lookup stage.. perhaps there is another stage i should use?

what would be really nice is a join stage that allows me to add join criteria like the following:

parent.field_id=child.field_id and (parent.term_dt>=child.eff_dt and parent.eff_dt<=child.term_dt)

rather than just a straight up parent.field_id=child.field_id. is there anything like that available for datastage?

thanks for your time.

[anbu]

After join add a transformer and use your expression

Code: Select all

parent.term_dt>=child.eff_dt and parent.eff_dt<=child.term_dt

as transformer constraint.

Or use lookup stage to do range lookup

[james garner]

thanks, i will try the transformer constraint... though on the lookup stage range lookup it only allows me to do a lookup 1 date field not on both field.

parent.eff_dt<=child.term_dt AND parent.eff_dt>=child.eff_dt

i would like to do this in the lookup stage if possible just dont see how.

[anbu]

I didn't notice that you are using two different date fields both in stream and reference. Then you cannot use lookup stage for range lookup

[james garner]

Right.. that seems to be my problem with the lookup, it cant perform a lookup on 2 overlapping date ranges..

[james garner]

Is there any other stage or methodology i could try to use?

[Sainath.Srinivasan]

You can do a sparse lookup with user-defined SQL containing your WHERE clause.

[anbu]

Did you try join and transformer stage?

Is your main and reference tables in the same instance? Then you can do it in sql.

[james garner]

I'm not familiar with a sparse lookup?