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