PX Range Lookup

Nick_6789 · Post by **Nick_6789** » Sat Aug 25, 2007 8:25 pm

Hi there,

I know there are various threads about Range lookup done in PX jobs. However, I can't seem to get an sql right were I to use the lookup directly from Db2 stage.

E.g.

Primary flow
========

Name, transaction_date

DB2 reference table
=============

transaction_id, min_date, max_date

How do I sorta pass in my prim flow 'transaction_date' field to the db2 sql statement? Does it go?

Select transaction_id, min_date, max_date
from reference_table
where ochestrate.transaction_date between min_date and max_date

Somehow I am still getting some error and ironing out the prob...

I thought of another solution, using lookup stage...

Where looking up with both min and max dates, I can insert a constraint in the lookup stage where transaction_date >= min_date and transaction_date <= max_date.

Appreciate the help. =)

ArndW · Post by **ArndW** » Sat Aug 25, 2007 9:00 pm

Try doing a simple single-key lookup to your DB2 stage. Activate the automatically generated SQL, and then click on user-defined so that ityou can edit the existing SQL. From that you will see the format and syntax and can add your own between condition easily.

Nick_6789 · Post by **Nick_6789** » Mon Aug 27, 2007 3:36 am

ArndW wrote:Try doing a simple single-key lookup to your DB2 stage. Activate the automatically generated SQL, and then click on user-defined so that ityou can edit the existing SQL. From that you will see the format and syntax and can add your own between condition easily.

Thanks for the advice.

I stuck to the direct lookup to db2 and it works. Thanks.