Range lookup issue
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 14
- Joined: Thu Dec 14, 2006 9:45 am
Range lookup issue
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)!!!!!
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)!!!!!
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 14
- Joined: Thu Dec 14, 2006 9:45 am
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 14
- Joined: Thu Dec 14, 2006 9:45 am
-
- Participant
- Posts: 14
- Joined: Thu Dec 14, 2006 9:45 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 14
- Joined: Thu Dec 14, 2006 9:45 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
In this case you require TWO key columns; id and date.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 14
- Joined: Thu Dec 14, 2006 9:45 am
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.
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.
ray.wurlod wrote: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. ...
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom