how to join the extra look up condition in the lookup

[parvathi]

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?

[DSguru2B]

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

Code: Select all

table1.date >= table2.startdt AND table1.date <= table2.enddt

[srinath]

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

[kris007]

What you are trying to do is a range lookup. You are well of using ODBC stage or a Universe stage. Search for range lookups..some key words are multi-row lookup, range look up.

Good luck.