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.
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)
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
