Comparing Dates between 2 Tables giving unexpected Results

[MM1655984008]

Hi,

I'm having an instance where 2 tables are involved for Querying and where clause is mainly based on dates and the resulting output is in Aggregation (the count)

Note: 2 Tables have different primary Keys, but there is a common key which joins both of them, and the SQL is running perfectly fine, but the Talend job unable to

SQL

SELECT COUNT(*), PT.PT_ID, ST.DAY_OF_REC FROM PRIMARY_TABLE PT INNER JOIN SECONDARY_TABLE ST ON PT.PT_ID=ST.PT_ID

WHERE ST.END_DATE_TIME >= PT.COMPLETED_DTM AND ST.START_DATE_TIME < PT.COMPLETED_DTM GROUP BY PT.PT_ID, ST.DAY_OF_REC ORDER BY 3

Talend

Primary Table --> ST (row15)

Lookup Table --> PT (row16)

TMAP expression filter

row16.COMPLETED_DTM != null 

&&

row16.COMPLETED_DTM.before(row15.END_DATE_TIME)

&&

row16.COMPLETED_DTM.after(row15.START_DATE_TIME)

And in Output, i'm only taking PT_ID & DAY_OF_REC columns, and finally after Tmap is executed, i see only one rec, as output.

If i include row16.COMPLETED_DTM or row15.END_DATE_TIME or row15.START_DATE_TIME in the out, then seeing all the records from PT table

Note: i'm new to talend, but not to Java

Thanks in advance for your help

[Anonymous]

Hi

Please upload a screenshot of tMap, it will be helpful for us to understand what you do on tMap.

Regards

Shong