How to join data with a BETWEEN function
Moderators: chulett, rschirm, roy
How to join data with a BETWEEN function
Hi all,
I would like to left outer join data from a 12million+ rows table onto my stream data, with a DATE from the stream into START_DATE and END_DATE in the 12m+ table.
select A.*, B.*
from A, B
left outer join B on A.key = B.key
where A.DATE >= B.START_DATE
and A.DATE < B.END_DATE
In a Join and Merge stage I cannot add any more constraints.
In a Lookup stage I could not join the data, only lookup.
1. What is the best way to get the 12m+ data? Connector/Lookup_file/...
2. How to join with a point in between 2 dates? Except SQL overwrite.
Thanks!
I would like to left outer join data from a 12million+ rows table onto my stream data, with a DATE from the stream into START_DATE and END_DATE in the 12m+ table.
select A.*, B.*
from A, B
left outer join B on A.key = B.key
where A.DATE >= B.START_DATE
and A.DATE < B.END_DATE
In a Join and Merge stage I cannot add any more constraints.
In a Lookup stage I could not join the data, only lookup.
1. What is the best way to get the 12m+ data? Connector/Lookup_file/...
2. How to join with a point in between 2 dates? Except SQL overwrite.
Thanks!
Source 1 is connector stage and stream.
Source 2 is Lookup_File and reference.
The stream needs to do a range lookup to the Lookup_File, with the lookup stage.
But I can't perform the range lookup with that combination.
It does work with 2 connector stages, but I would like to use the Lookup_file because I would like to load it once and re-use it in other jobs.
Source 2 is Lookup_File and reference.
The stream needs to do a range lookup to the Lookup_File, with the lookup stage.
But I can't perform the range lookup with that combination.
It does work with 2 connector stages, but I would like to use the Lookup_file because I would like to load it once and re-use it in other jobs.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You should be able to; the range lookup is performed against a virtual data set loaded into memory no matter what the supporting stage type is (except, possibly, Lookup File Set stage).dveltman1 wrote:But I can't perform the range lookup with that combination.
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.