Pattern Matching
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 53
- Joined: Fri Mar 07, 2008 1:17 am
Pattern Matching
Hi All,
We have a requirement where in after the look up, not only the records with exact match but also any source records matching the pattern in the look up dataset should also pass.
We used the below option however there can be performance issues as the source data cann be huge:
Have a left outer join based on some dummy key column (generated on either side of the join). This will resut in cartesian product. Then using a transformer, using count function doing the pattern match and placing a constraint accordingly. the output will be 1 for pattern matched records and 0 for unmatched records.
Is there any other approach which can give better performance? Please help
We have a requirement where in after the look up, not only the records with exact match but also any source records matching the pattern in the look up dataset should also pass.
We used the below option however there can be performance issues as the source data cann be huge:
Have a left outer join based on some dummy key column (generated on either side of the join). This will resut in cartesian product. Then using a transformer, using count function doing the pattern match and placing a constraint accordingly. the output will be 1 for pattern matched records and 0 for unmatched records.
Is there any other approach which can give better performance? Please help
ETL DEVELOPER
-
- Participant
- Posts: 53
- Joined: Fri Mar 07, 2008 1:17 am
-
- Participant
- Posts: 53
- Joined: Fri Mar 07, 2008 1:17 am
-
- Premium Member
- Posts: 258
- Joined: Tue Jul 04, 2006 10:35 pm
- Location: Toronto
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I don't believe DataStage can do this. You might try using a sparse lookup (assuming you have a database that supports the same) with LIKE in the user-defined SQL.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 53
- Joined: Fri Mar 07, 2008 1:17 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Pattern Matching
Hi,datastagedw wrote:Hi All,
Have a left outer join based on some dummy key column (generated on either side of the join). This will resut in cartesian product. Then using a transformer, using count function doing the pattern match and placing a constraint accordingly. can give better performance? Please help
Can you please explain this approach little bit?
My source file contains below records:
source
--------
abc
bca
axdbc
cbhss
bcda
Reference
----------
bc
My output should be
abc
bca
axdbc
Thanks
pandeeswaran
Hi Ray,ray.wurlod wrote: You might try using a sparse lookup (assuming you have a database that supports the same) with LIKE in the user-defined SQL.
I have tried the sparse lookup like below using oracle tables:
Source table contains a field VAL
and the records are
abc
bca
abcde
be
Reference table contains a field VAL and it contains only one record bc.
So the result should be
abc
bca
abcde
Job design:
Code: Select all
src table------------lkp stage(sparse)----------->seq file
|
|
ref table
Code: Select all
SELECT VAL FROM WHERE VAL LIKE '%ORCHESTRATE.VAL%'
Code: Select all
SELECT VAL FROM src
Please help me.
Thanks
pandeeswaran
-
- Premium Member
- Posts: 258
- Joined: Tue Jul 04, 2006 10:35 pm
- Location: Toronto