Hi all,
I want to look up with a column of a particular table1 with another table2 column and also check that also check that the date column of table 2 is in between the 2 date columns of table 1
How can i do this in datastage?
The look up that i have used looks like
select table2.col3 where table1.colm1=table2.colm1 and table1.date between table2.startdt and table2.enddt
oraclestage------->transformer----->target
tabl.colm1
| tabbl2.colm1
table2.colm3
|
target look up
in this type of scenario how can we use the data range condition?
how to join the extra look up condition in the lookup
Moderators: chulett, rschirm, roy
If you could do this in the database stage itself then you dont need to worry about lookup stage and the extra date condition. If you have to implement it in datastage then do a lookup on the second table, get all the results and constraint it in the transformer using the date range.
Something like
Something like
Code: Select all
table1.date >= table2.startdt AND table1.date <= table2.enddt
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Take ODBC lookup and make colm1,table2.startdt,table2.enddt as keys and in user defined query use
select table2.col3 from where table2.colm1= ? and table2.startdt > ? and table1.date < ?
and in the transformer give the values for table2.colm1 as table1.colm1, for table2.startdt as table1.date and for table2.enddt as table1.date
select table2.col3 from where table2.colm1= ? and table2.startdt > ? and table1.date < ?
and in the transformer give the values for table2.colm1 as table1.colm1, for table2.startdt as table1.date and for table2.enddt as table1.date