I want to perform a date range lookup Something like
Code: Select all
Source.key1 = lkp.key1 and
source.key2 = lkp.key2 and
lkp.EFF_START_DT <= Source.EFF_DT and
lkp.EFF_END_DT >= Source.EFF_DT
And based upon the suggestions from this forum, i tried using Universe Stage also, but it doesnt improve the performance much.
And in some other posts viewtopic.php?t=102858, DSguru2B has mentioned that we can do this with the Hashed File by intorducing a dummy Key Filed and match it with @INROWNUM
Code: Select all
if NOT(RefLink.NOTFOUND) AND main_id = lookup_id AND ICONV(dt_record,"D/MDY[2,2,4]") >= ICONV(dt_start,"D/MDY[2,2,4]") AND ICONV(dt_record,"D/MDY[2,2,4]") <= ICONV(dt_end,"D/MDY[2,2,4]") then 'P' else 'N'
1) If we mention KEY1 and KEY2 alone as key columns in the Hashed file , then the values can be overwritten.
Code: Select all
For Ex.
Key1$Key2$Desc$Eff_Start_Dt$Eff_End_Dt
A$B$TestDesc$01/01/2005$12/31/2005
A$B$TestDesc1$01/01/2006$12/31/2006
2) If we make the Date fields also as KeyColumns and if we pass EFF_DT form the source, the lookup wont match because it will try to do a equivalent match.
3) This will be same case, if we introduce a dummy sequential key field in Hashed file and match it with the @INROWNUM.
Please Suggest me how to proceed with this.
Note :
My Source and target is having around 1 million rows.