topic Re: How to use Between in On Operator in tMap for Date_Ranges (between source and lookup) in Talend Studio

How to use Between in On Operator in tMap for Date_Ranges (between source and lookup)

Mayur2 — Fri, 15 Nov 2024 22:59:05 GMT

Hi Team,

I have a scenario :

1. Select * from A

inner join B on A.Code = B.Code and A.Date Between B.From_Date and B.To_Date

I tried the expression at lookup side , somehow I am getting error, may be I am making some syntax issue.

I am unable to achieve this can you please assist.

As a turn around, i tried

2..Select * from A

inner join B on A.Code = B.Code

where A.Date Between B.From_Date and B.To_Date

This i am able to achieve in Expression inside Tmap at output side,

Here the issue is I am filtering the records after join , ideally I need to operate it on ON condition.

Please assist.

Re: How to use Between in On Operator in tMap for Date_Ranges (between source and lookup)

Anonymous — Thu, 28 Apr 2022 03:08:07 GMT

The tMap expression should use Java syntax, it does not allow SQL operator between ... in. My idea is to do inner join based on A.Code = B.Code on tMap, select all fields of A and B.From_Date and B.To_Date in the output table, add a filter expression in the output table on tMap or filter the data using tFilterRow.

Regards

Shong

Re: How to use Between in On Operator in tMap for Date_Ranges (between source and lookup)

TCalabro1652773885 — Wed, 01 Jun 2022 05:29:51 GMT

I'm confronting the particular issue. Did You sort out some technique for looking out for this, expecting this is the case will you share the methodology?

MyFordBenefits.Com

Re: How to use Between in On Operator in tMap for Date_Ranges (between source and lookup)

Mayur2 — Wed, 22 Jun 2022 08:41:27 GMT

Yes, Inside tmap : The expression you set on Left side will act as AND clause while expressions you set on right side would act as where clause