12 Mapping and Manipulating Data
12.7 Selecting and Filtering Rows
To filter out or select rows for extraction or replication, use the FILTER and WHERE clauses of the TABLE and MAP parameters.
The FILTER clause offers you more functionality than the WHERE clause because you can employ any of the Oracle GoldenGate column conversion functions, whereas the WHERE clause accepts basic WHERE operators.
12.7.1 Selecting Rows with a FILTER Clause
Use a FILTER clause to select rows based on a numeric value by using basic operators or one or more Oracle GoldenGate column-conversion functions.
The syntax for FILTER in a TABLE statement is as follows:
TABLE source_table, , FILTER (
[, ON INSERT | ON UPDATE| ON DELETE]
[, IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE]
, filter_clause);
The sytax for FILTER in a MAP statement is as follows and includes an error-handling option.
MAP source_table, TARGET target_table, , FILTER (
[, ON INSERT | ON UPDATE| ON DELETE]
[, IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE]
[, RAISEERROR error_number]
, filter_clause);
Valid FILTER clause elements are the following:
■ An Oracle GoldenGate column-conversion function. These functions are built into Oracle GoldenGate so that you can perform tests, manipulate data, retrieve values, and so forth. See Section 12.12, "Testing and Transforming Data" for more
information about Oracle GoldenGate conversion functions.
■ Numbers
■ Columns that contain numbers
■ Functions that return numbers
Note: To filter a column based on a string, use one of the Oracle GoldenGate string functions or use a WHERE clause.
■ Arithmetic operators:
– + (plus) – - (minus) – * (multiply) – / (divide) – \ (remainder)
■ Comparison operators:
– > (greater than)
– >= (greater than or equal) – < (less than)
– <= (less than or equal) – = (equal)
– <> (not equal)
– Results derived from comparisons can be zero (indicating FALSE) or non-zero (indicating TRUE).
■ Parentheses (for grouping results in the expression)
■ Conjunction operators: AND, OR
Use the following FILTER options to specify which SQL operations a filter clause affects. Any of these options can be combined.
ON INSERT | ON UPDATE | ON DELETE
IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE
Use the RAISEERROR option of FILTER in the MAP parameter to generate a user-defined error when the filter fails. This option is useful when you need to trigger an event in response to the failure.
Example 12–1 Calling the @COMPUTE Function
The following example calls the @COMPUTE function to extract records in which the price multiplied by the amount exceeds 10,000.
MAP SALES.TCUSTORD, TARGET SALES.TORD,
FILTER (@COMPUTE (PRODUCT_PRICE * PRODUCT_AMOUNT) > 10000);
Example 12–2 Calling the @STREQ Function
The following uses the @STREQ function to extract records where the value of a character column is 'JOE'.
TABLE ACCT.TCUSTORD, FILTER (@STREQ ("Name", 'joe') > 0);
Example 12–3 Selecting Records
The following selects records in which the AMOUNT column is greater than 50 and executes the filter on UPDATE and DELETE operations.
TABLE ACT.TCUSTORD, FILTER (ON UPDATE, ON DELETE, AMOUNT > 50);
You can use the @RANGE function to divide the processing workload among multiple FILTER clauses, using separate TABLE or MAP statements. For example, the following
splits the replication workload into two ranges (between two Replicat processes or two threads of a coordinated Replicat) based on the ID column of the source acct table.
Example 12–4 Using the @RANGE Function (Replicat group 1 parameter file)
MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (1, 2, ID));
(Replicat group 2 parameter file)
MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (2, 2, ID));
You can combine several FILTER clauses in one MAP or TABLE statement, as shown in Table 12–6, which shows part of a Replicat parameter file. Oracle GoldenGate executes the filters in the order listed, until one fails or until all are passed. If one filter fails, they all fail.
12.7.2 Selecting Rows with a WHERE Clause
Use any of the elements in Table 12–7 in a WHERE clause to select or exclude rows (or both) based on a conditional statement. Each WHERE clause must be enclosed within parentheses. Literals must be enclosed within single quotes.
Table 12–6 Using Multiple FILTER Statements
Parameter file Description
REPERROR (9999, EXCEPTION) Raises an exception for the specified error.
MAP OWNER.SRCTAB, TARGET OWNER.TARGTAB,
Starts the MAP statement.
SQLEXEC (ID CHECK, ON UPDATE, QUERY ’ SELECT COUNT FROM TARGTAB ’
’WHERE PKCOL = :P1 ’, PARAMS (P1 = PKCOL)),
Performs a query to retrieve the present value of the COUNT column whenever an update is encountered.
FILTER (BALANCE > 15000), Uses a FILTER clause to select rows where the balance is greater than 15000.
FILTER (ON UPDATE, @BEFORE (COUNT) = CHECK.COUNT)
Uses another FILTER clause to ensure that the value of the source COUNT column before an update matches the value in the target column before applying the target update.
; The semicolon concludes the MAP
statement.
MAP OWNER.SRCTAB, TARGET OWNER.TARGEXC, EXCEPTIONSONLY, COLMAP (USEDEFAULTS,
ERRTYPE = ’UPDATE FILTER FAILED’);
Designates an exceptions MAP statement. The REPERROR clause for error 9999 ensures that the exceptions map to TARGEXC will be executed.
Table 12–7 Permissible WHERE Operators
Element Examples
Column names PRODUCT_AMT Numeric values -123, 5500.123
Oracle GoldenGate does not support FILTER for columns that have a multi-byte character set or a character set that is incompatible with the character set of the local operating system.
Arithmetic operators and floating-point data types are not supported by WHERE. To use more complex selection conditions, use a FILTER clause or a user exit routine. See Section 17.3, "Using User Exits to Extend Oracle GoldenGate Capabilities" for more information.
The syntax for WHERE is identical in the TABLE and MAP statements:
TABLE table, WHERE (clause);
MAP source_table, TARGET target_table, WHERE (clause);
12.7.3 Considerations for Selecting Rows with FILTER and WHERE
The following suggestions can help you create a successful selection clause.
12.7.3.1 Ensuring Data Availability for Filters
If the database only logs values for changed columns to the transaction log, there can be errors if any of the unchanged columns are referenced by selection criteria. Oracle GoldenGate ignores such row operations, outputs them to the discard file, and issues a warning.
To avoid missing-column errors, create your selection conditions as follows:
■ Use only primary-key columns as selection criteria, if possible.
■ Make required column values available by enabling supplemental logging for those columns. Alternatively, you can use the FETCHCOLS or FETCHCOLSEXCEPT option of the TABLE parameter. These options are valid for all supported databases except SQL/MX. They query the database to fetch the values if they are not present in the log. To retrieve the values before the FILTER or WHERE clause is executed, include the FETCHBEFOREFILTER option in the TABLE statement before the FILTER or WHERE clause. For example:
TABLE DEMO.PEOPLE, FETCHBEFOREFILTER, FETCHCOLS (age), FILTER (age > 50);
Literal strings ’AUTO’, ’Ca’
Built-in column tests @NULL, @PRESENT, @ABSENT (column is null, present or absent in the row). These tests are built into Oracle GoldenGate. See Section 12.7.3,
"Considerations for Selecting Rows with FILTER and WHERE".
Comparison operators
=, <>, >, <, >=, <=
Conjunctive operators AND, OR
Grouping parentheses Use open and close parentheses ( ) for logical grouping of multiple elements.
Note: The examples in this section assume a case-insensitive database.
Table 12–7 (Cont.) Permissible WHERE Operators
Element Examples
■ Test for a column's presence first, then for the column's value. To test for a column's presence, use the following syntax.
column_name {= | <>} {@PRESENT | @ABSENT}
The following example returns all records when the amount column is over 10,000 and does not cause a record to be discarded when amount is absent.
WHERE (amount = @PRESENT AND amount > 10000)
12.7.3.2 Comparing Column Values
To ensure that elements used in a comparison match, compare appropriate column types:
■ Character columns to literal strings.
■ Numeric columns to numeric values, which can include a sign and decimal point.
■ Date and time columns to literal strings, using the format in which the column is retrieved by the application.
12.7.3.3 Testing for NULL Values
To evaluate columns for NULL values, use the following syntax.
column {= | <>} @NULL
The following returns TRUE if the column is NULL, and FALSE for all other cases (including a column missing from the record).
WHERE (amount = @NULL)
The following returns TRUE only if the column is present in the record and not NULL.
WHERE (amount = @PRESENT AND amount <> @NULL)