Expression handling - Connect_CDC - connect_cdc_mimix_share - Latest

The following sections provides details about using expressions when you make complex row selection and column transformation decisions. The Expression Handler for Connect CDC is platform independent and includes a number of built-in expressions to handle common transformations. This topic is introduced in the Advanced User Guide.

You can define expressions in a target table mapping, using the Connect CDC Director. These expressions are similar to the value expressions defined in the SQL standard. You can use expressions to model the following:

1. A conditional expression in the target table mapping returns a Boolean value that is tested at runtime for each data row to determine whether it will be sent to the target table that is defined in that table mapping.

2. An expression or data transformation method defines a transformation to be applied to a column mapping between a source column and a target column. This kind of expression returns a runtime value for the target column that is compatible with the target column as to its type and length.

These two expression types are really only different in the type of the value that the expression returns. In fact, they are both functions that:

• Return a value of a certain type.

• Contain any number of input parameters, either implied or explicit.

• Within the “body” of the expression, have an arbitrary number of constants, column references, and calculations on those constants and columns. These calculations could contain calls to methods returning intermediate values.

The Connect CDC Expression Handler parses expressions that you input into the model. The Expression Handler then creates runtime code that calculates a result for expressions.

Examples of Simple Expressions

•       Send this row to a target table if the following gate condition returns true:

AMOUNT  > 1000 AND (STATE_CODE EQ ‘MA’ OR STATE_CODE EQ ‘NY’);

•       Put the following transformed source data into the target column called SALARY to which the following expression is mapped:

PAY_0RATE * HOURS_WORKED + PAY_RATE * 1.5 * HOURS_OVERTIME;

• Put the following transformed source data into the mapped target column called FULL_NAME. This invokes the system built-in methods concat (concatenates 2 or more strings) and SUBSTRING:

concat (FIRST_NAME, concat(‘ ‘, SUBSTRING(MIDDLE_NAME,1,1), ‘ ‘),       LAST_NAME);

•       Put the following transformed source data into the mapped target column; this invokes a user method:

CALC_MY_SALARY(HOURS_WORKED, HOURS_OVERTIME, PAY_RATE);

In these simple expressions, there is always an implied return value and an implied input of the current data row: in effect, it defines a function (or method) with implied input values (any column value in the current row) and a discrete return value.

The user can define a more formally and explicitly described function or method, (see User Procedures) also known as user methods or complex expressions). The example above, CALC_MY_SALARY, shows a complex expression where the inputs to the method are explicit.

SQL: 1999 – Understanding Relational Language Components, by Jim Melton and Alan R. Simon