lookup condition and constraints

[kjaouhari]

Hello dsxians !

In first I wish you a happy new year 2007 !

I'm trying to do a lookup that work with 2 oracle tables:
the first is input and the second is the reference_date and I want all the input rows if the table reference_date is empty.

Then if the reference_date is not empty we use the lookup but if is empty we want to pass all rows.
And I don't want to use the reject link

Thanks in advance for your ideas !

[DSguru2B]

If i understood your requirement then do this:
Get that reference date in a UserStatus. Check it in a stage variable. If its empty then NOT(lookup.NOTFOUND) else @TRUE. Specify stage variable name in the constraint.

[ray.wurlod]

Do it all in SQL. Use a CASE with a nested SELECT COUNT(Key)... to determine whether the reference table is empty. Deliver the results to DataStage.

[Pavan_Yelugula]

you can do this with a combination of small basic code and a set of jobs...
Step 1: Rite a small basic code to identify if the reference data table has records in it or not.
Step 2 :create two jobs one with look up and one without lookup which is a straight load
Step 3 : In the basic code put a if loop and call the appropriate job based on what your step 1 returns

Thanks
Pavan

[kjaouhari]

Thanks all !