How to do look for the greater than or less than date ranges

[johncool]

Table_A

PROD1, MFGSTARTDATE, MFGENDDATE

Table_B

PROD1, QADATE

I have to pass the records only when the 'QADATE' is
between 'MFGSTARTDATE' and 'MFGENDDATE'
Can anyone tell me which stage to use to achive this.
Thanks in advance.

[gautham]

we can achive this by writing a constraint in the target

[meena]

Hi,
If tables are from the same same schema you can do it SQL statement with where clause in it.
Else if tables have different schemas then you have use join stage to join the table based on the key and use filter stage for where clause.

[ray.wurlod]

Version 8.0 supports range lookup. Earlier versions do not, unless you resort to user-defined SQL.

[Kirtikumar]

If you do search for range lookups, you will find many posts on this as it is discussed several times. You will get the solution in search itself

[bmadhav]

U cannot do this in the current version of PX. U may want to call a server container in the PX job and do the range lookup in the server container by using a transformer stage and the appropriate database stage and then return the result back to the PX job.

[Kirtikumar]

Instead of the server shared container, check if the sparse lookup can be used. Sparse lookup allows you to fire queries at runtime. So you have to define Table_A as reference link with key columns mapped to QADATE.
Then in SQL in TABLEA, add the query which will have range condition.

But keep in mind - sparse lookups hit the performance. In case of sparse lookup, query is prepared with the value coming from stream link and then fires it on database.

[ray.wurlod]

Even (especially) sparse lookups only do equi-joins.