Pattern Matching Conditions
By David Allan-Oracle on Aug 13, 2009
The Oracle database has a range of powerful regular expression based pattern matching functions that can be used to filter and match data, in OWB for example conditions can be used in many places such as filters and joins. This post illustrates how to incorporate pattern matching conditions into OWB today in mappings you design, the data rules within OWB utilize these routines for when they match data (identification numbers, telephone numbers etc.). As the condition types evolved through the releases of the database as did support in OWB, but there is a little hoop to jump which was pointed out from Peter at Rittman Mead Consulting.
The OWB validation for condition varies, so the expression may work in a filter but might be slightly different in a join clause. The REGEXP_LIKE condition is not handled fantastically in the join condition (putting it politely), so we have to do a wee (Scottish accent coming out there) workaround.
So let's look at a basic example from the manuals, the following query returns the first and last names for those employees with a first name of Steven or Stephen (where first_name begins with Ste and ends with en and in between is either v or ph), simple right:
SELECT first_name, last_name
WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$')
ORDER BY first_name, last_name;
This uses the EMPLOYEES table in the HR demo schema, it results in the following;
Now building the same mapping with the same condition in OWB works fine;
We get the map validated fine and code generated, great so far.
Now we try using the JOIN operator and have a fabricated example where the regular expressions to be matched are in a table MATCHING_TAB and we will join this with the EMPLOYEES and use the REGEXP_LIKE function to match;
When you try generating the code for this map design you will get an error stating the join condition is not valid and that a join condition must be a boolean condition. The OWB code generator is parsing your conditions here and has support for a fixed number of condition types, to use REGEXP_LIKE we have to be a little creative.
So you can't just do REGEXP_LIKE(...) =1 or anything, one approach is to use the CASE statement, to test the pattern matching condition and return 1 if matched and 0 otherwise, then we can test for 1=1...phew! Here is a working design that provides OWB with a valid boolean condition;
You can see the join condition uses the FIRST_NAME column from EMPLOYEES and the EXPR column holding the regular expressions from the matching tab in the REGEXP_LIKE function. A quick run through a query that others will hopefully find useful.