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

[Mayur2]

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.

[Anonymous]

Hi

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

[TCalabro1652773885]

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

[Mayur2]

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