topic Re: using date ranges in join condition of a tmap in Talend Studio

using date ranges in join condition of a tmap

Anonymous — Sat, 16 Nov 2024 12:21:34 GMT

hello everyone
I want to join two tables based on the date ranges. I have tradedate column in main link and STARTDATE and ENDDATE columns in lookup link. I need to check whether tradedate is on or after the STARTDATE and on or before ENDDATE. I am not able to run the query to meet this requirement inside the instance because of heapspace error problem . Plz help me how to do this inside tMap component or some other components.

Thanks in advance.
S.Arasu.

Re: using date ranges in join condition of a tmap

Anonymous — Mon, 20 Feb 2012 17:41:05 GMT

Hi,
If you're using an RDBMS, use this query in an input component.
select t.*
from Td t
WHERE EXISTS
(select * from TdLookup tl
where t.tdDate >= tl.tdLookupStartDate AND t.tdDate <= tl.tdLookupEndDate)

Re: using date ranges in join condition of a tmap

Anonymous — Thu, 01 Mar 2012 10:47:29 GMT

Hi,
I got the same problem. But I can't apply the walkerca solution as the 2 streams (main and lookup) are coming from 2 different databases.
I solved partialy the problem with a tmap component. I filter my loopup stream with an date between expression. (cf pictures above). This solution works: the output 'AVEC_ACTIVITE' gets all the rows that are matching the date between condition.
But I want to get all the rows that are not matching the date between expression too. I made a 2nd output 'SANS_ACTIVITE' with the 'catch output reject' activated bot no rows are coming in.
I tryed a 2nd trick by putting the date between condition in the output 'AVEC_ACTIVITE' but in this case, too much rows are streaming in the 2nd output because these are the result of the cross join of the two inputs.
For example:
Input1 (input with date to compare):
A1 - 01/01/2012
A2 - 02/01/2012
A3 - 03/01/2012
A4 - 05/01/2012
Input2 (imput with date range):
B1 - 31/12/2012 - 02/01/2012
B2 - 02/01/2012 - 04/01/2012
output1 (dates in range (inner join)):
A1 - B1
A2 - B2
A3 - B2
output2 (rejects from inner join):
A4

Any idea ?
I'm using TOS 5.0.1
Thanks