Range lookup issue

[datastagequestions]

Hi,

I am trying to do the lookup but not getting the correct results.

1) I have source table which has : Id, Eff_date & Term_date, name
2) In lookup stage : Id, eff_date & Term_date,name

I am using UVstage for lookup. My requirement is:
if Srce.id=Lkup.id and Srce.Eff_date between (Lkup.eff_date & Lkup.term_date) and Srce.Term_date between (Lkup.eff_date & Lkup.term_Date) then populate Srce.name with Lkup.name.

I searched the forum for 'Range Lookup', but couldn't find any posts with this kind of requirement.

Any help will be appreciated(including pointing to right threads)!!!!!

[asorrell]

I can't remember for sure - and I can't access DataStage right now to confirm it, but isn't range look up a PX only capability?

[chulett]

Native range lookup, yes - PX only. You do it in Server different ways, which should all be found by an exact search for "range lookup" where job type is server. The UV stage sitting on an account-based hashed file (so you can use 'sql') is one such solution noted out there.

What problem are you having, exactly? You haven't said other than saying you are "not getting correct results" which is not all that helpful.

[datastagequestions]

In UVstage, I made Id,eff_date,term_date as key fields and used user defined SQL : select * from hashfile where id=? and ?between eff_date and term_date. I can view data in UVstage so I know UVstage is set up correctly. I guess I am not using the logic the right way.

In Tfmr I wrote the logic :

if srce.eff_date>=lkup.eff_date and <=lkup.term_date then If srce.term_date>lkup.eff_date and <=lkup.ter_date then lkup.name else null.

In my target table all rows has null name.
Thanks.

[ray.wurlod]

Have you marked id and the date as Key columns on the reference link?

[chulett]

Tried changing that "select *" to a specific list of fields? One that exactly matches your columns in the stage?

[datastagequestions]

Have you marked id and the date as Key columns on the reference link? ...

Yes, I marked Id, eff_date & term_Date in lookup as key columns.

[datastagequestions]

Tried changing that "select *" to a specific list of fields? One that exactly matches your columns in the stage? ...

My select statement is :select id,eff_date,term_date, name from lkup where "....."

[ray.wurlod]

So you have three key columns and two parameter markers in the WHERE clause? That won't work. Your keys should be ID and TheDate (where you want to return rows where TheDate is between eff_date and term_date).

[Sainath.Srinivasan]

Create 5 columns from the reference as keys (1 for key, 2 each for the dates).

[datastagequestions]

Hi, Can you please be more specific about it. I have 4 columns in Lookup: Id,eff_date,Term_date & name. I made id and both dates as key columns. Thanks.

Create 5 columns from the reference as keys (1 for key, 2 each for the dates).

[ray.wurlod]

I am assuming you are supplying one date and returning rows for which that date is between eff_date and term_date.

In this case you require TWO key columns; id and date.

[datastagequestions]

The columns in both Source & Lookup are:
Source : Id, Eff_date, Term_date,name
Lookup : Id, eff_date,Term_date,name

My requirement is : if Srce.Id =Lookup.Id and Srce.Eff_date & Srce.Term_Date between Lookup. eff_date & term_date then replace srce.name with Lookup.name.

Hope this helps!
Thanks.

I am assuming you are supplying one date and returning rows for which that date is between eff_date and term_date.

In this case you require TWO key columns; id and date. ...

[ray.wurlod]

OK, three keys, and therefore three comparisons in the lookup SQL.

Please post the SQL statement on the reference link.

[Sainath.Srinivasan]

Five keys.

Id
Eff_Date_From
Eff_Date_To
Term_Date_From
Term_Date_To