Implementing OR condition
Moderators: chulett, rschirm, roy
Implementing OR condition
Hi,
I am new to Datastage PX and wanted to know how to implement OR condition.
I have 3 lookup's to make on 3 different files. If record is not not found in first lookup i should not drop or reject the record , i have to continue to search in other 2 lookup's.
Can i implement with single lookup stage or join stage OR i have to use 3 stages to make lookup on 3 files by specifying inner join.
Regards
DK.
I am new to Datastage PX and wanted to know how to implement OR condition.
I have 3 lookup's to make on 3 different files. If record is not not found in first lookup i should not drop or reject the record , i have to continue to search in other 2 lookup's.
Can i implement with single lookup stage or join stage OR i have to use 3 stages to make lookup on 3 files by specifying inner join.
Regards
DK.
Praneet
Re: Implementing OR condition
It can be implement in different ways based on requirment.
You can use 3 lookup stage.
In first lookup,lookup on first file and reject the unmatched record.
In second stage, lookup only for rejected record and reject the unmatched record.
in third stage, lookup the rejected records from second lookup.
Then use the funnel stage to collect all the matching records from 3 loopup stages.
You can use 3 lookup stage.
In first lookup,lookup on first file and reject the unmatched record.
In second stage, lookup only for rejected record and reject the unmatched record.
in third stage, lookup the rejected records from second lookup.
Then use the funnel stage to collect all the matching records from 3 loopup stages.
Nirdesh Kumar
Or you can do this in single lookup stage and three lookup dataset.
Do lookup against all the three reference with the corresponding key.
With "Keep" option on Lookup failure.
And use transformer to find the lookup match in the order you need and do the business later.
Do lookup against all the three reference with the corresponding key.
With "Keep" option on Lookup failure.
And use transformer to find the lookup match in the order you need and do the business later.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
HI,
First ption is ok. In second option in transformer how can i know is lookup passed or failed. In lookup datasets i have only Key column (sigle column).
If lookup pass in any of the 3 lookup files , i have to write one column coming from source to another file which will be used in different job.
Regards
Prannet.
First ption is ok. In second option in transformer how can i know is lookup passed or failed. In lookup datasets i have only Key column (sigle column).
If lookup pass in any of the 3 lookup files , i have to write one column coming from source to another file which will be used in different job.
Regards
Prannet.
Praneet
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
HI,
First ption is ok. In second option in transformer how can i know is lookup passed or failed. In lookup datasets i have only Key column (sigle column).
If lookup pass in any of the 3 lookup files , i have to write one column coming from source to another file which will be used in different job.
Regards
Prannet.
First ption is ok. In second option in transformer how can i know is lookup passed or failed. In lookup datasets i have only Key column (sigle column).
If lookup pass in any of the 3 lookup files , i have to write one column coming from source to another file which will be used in different job.
Regards
Prannet.
Praneet
-
- Premium Member
- Posts: 72
- Joined: Mon Jul 06, 2009 9:34 pm
- Location: Sydney
You can do the three looks ups in three different lookup stages with the 'lookup failure' condition as continue. You are then affectively doing a left outer join on all three stages, you can capture flags in the three lookupsatges that meet the criteria and apply the required condition in a transformer. This gives you more flexibility.
Prakash Dasika
ETL Consultant
Sydney
Australia
ETL Consultant
Sydney
Australia
Try adding a dummy column in the references. And you can use the dummy column to check the lookup condition.
If you cant add the dummy column...
Then, you may need three lookup stages.
On failure of the first lookup, reject the recorcs to second.
On failure of second lookup, reject the records to third.
Finally Funnel them all all up to do your business logic.
If you cant add the dummy column...
Then, you may need three lookup stages.
On failure of the first lookup, reject the recorcs to second.
On failure of second lookup, reject the records to third.
Finally Funnel them all all up to do your business logic.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'