Page 1 of 1
How to do look for the greater than or less than date ranges
Posted: Wed Oct 18, 2006 10:03 pm
by 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.
Re: How to do look for the greater than or less than date ra
Posted: Wed Oct 18, 2006 10:17 pm
by gautham
we can achive this by writing a constraint in the target
Posted: Wed Oct 18, 2006 10:24 pm
by 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.
Posted: Wed Oct 18, 2006 11:46 pm
by ray.wurlod
Version 8.0 supports range lookup. Earlier versions do not, unless you resort to user-defined SQL.
Posted: Thu Oct 19, 2006 12:53 am
by 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
Posted: Sun Oct 22, 2006 10:10 am
by 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.
Posted: Mon Oct 23, 2006 1:35 am
by 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.
Posted: Mon Oct 23, 2006 6:57 am
by ray.wurlod
Even (especially) sparse lookups only do equi-joins.