left join with between date condition

[vg]

Hi,

I need help in implementing following join logic.

Select * from Table A
Left outer join table B
on A.column1 = B.column1
and ((A.date1 between B.date1 and B.date2) or (A.date2 between B.date1 and B.date2))

First I used a Join and then used filter after that for date condition, but it gives me a wrong result.

Here both table A and B are files.

I under stood that left join and both date conditions should be done within Join.

Please let me know if there is way to get the correct result.

[srinivas.g]

Use range lookup.

[vg]

I need pass rows that saitsfy either of the two range date conditions ((A.date1 between B.date1 and B.date2) or (A.date2 between B.date1 and B.date2)).

if i use 2 range conditions in lookup its treating as ''AND''....but my requirement is to use ''OR''..please let know how to achice this...

[elsont]

Then duplicate the input stream into two using copy stage. Do the first lookup on the first stream, second lookup on the second stream. Now funnel into one and remove duplicates

[ssreeni3]

Try this:
Please ensure In Sql query:
Between Minimum_Value AND Maximum_Value

Then It works properly.
---------------------------------------------------------------
Sreeni3

[jwiles]

You stated that the join followed by a filter did not work. What keys did you join on and what was the filter condition that you used? Was the data properly partitioned and sorted before the join?

Regards,