How you specify lookup - Connect_CDC - connect_cdc_mimix_share - Latest

In the diagram below, the table T1 is the trigger table. Construct the lookup so that when an update, insert or delete is made in the trigger table, this value is fetched from the lookup table T2 and sent to the returned column in T3. Column C is used as the key to access table T2 and return column F for inclusion in data distribution.

In the example used on the following pages, the trigger table Employee has employee information, consisting of these columns:

The lookup table has these columns:

Of these columns, only some are used in the mapping.

1. Right-click the sending table then select Select for Distribution>This table only, from the menu that displays to select the table.

   If you do not have matching names in the target table, you get a message similar to the following:

2. Click OK.

3. On the Sending table Properties page, Mapping tab, specify the Method.

   In the example, the Copy Column method fills in for the Employee_id, Employee_pic, and Employee_history columns since there are matching column names.

4. Select Expression from the list of methods, then click Edit source.

5. Specify the Target Column Expression information.

   1. Select Lookup Table from the Method list.

   2. Click Insert.

   3. Place your cursor inside the parentheses to define the parameters for the lookup method.

   4. Insert the lookup table, input lookup key column, returned column and key value, in the format:

      String LookupDatabaseTable(String lookup table, String input lookup key column, String returned column, String key value);

      Enclose each value with single quotes, separate by commas, and end with a semi-colon.

      If the key value is not unique and more than one row is selected, then only the first row is fetched and used for finding the value associated with the returned column. If the key value is not found in the lookup table, then the method returns "?".

   5. Insert the lookup table value, the fully qualified name (that is, database_name.schema_name.table_name) of the lookup table (qatest.dbo.DEPT in the example).

   6. Insert the input lookup key column, the unqualified name of the key to the lookup table. This will be used as the key for the search in the lookup table (DEPTNO in the example).

   7. Insert the returned column, the unqualified named lookup column, the value for which is to be returned from the method (DEPT_NAME from the lookup table in the example).

   8. Insert the key value, the search key specified as a character string, to search for in the lookup table using the column specified by input lookup key column from the trigger table (DEPARTMENT from the trigger table in the example). Select the column name in the Column field on the Target Column Expression dialog).

      Note: The columns in the lookup table specified by input lookup key column and returned column can be any datatype supported in the Expression Handler, but the values MUST be specified as characters.

      Since the method uses character strings from the key value and as the return target value, you are required to convert  int, real, double, date, etc., key value to char values.

      Since the key value in our example is not a char, specify a built-in method to convert it. In this case, the method Numeric to character converts the integer to character. Select the method from the list then click Insert.

      The built-in method is inserted with its own set of parentheses. Use these steps as shown in the following example:

      1. Place your cursor within the key value parentheses.

      2. Select the name of the column of your key value from the Column field.

      3. Click Insert.

      4. Validate.

      Alternately, if you had wished to perform a lookup on DEPT_RANKING, its datatype is integer, and as such, requires conversion, Character to numeric integer. 

      When you select Character to numeric integer from the list in the Method field and click the Insert button, you see tonumber in the expression window, which wraps the entire expression:

      tonumber()

      Then select the lookup table method, which has its own set of parentheses:

      tonumber(LookupDatabaseTable( ) )

   9. Continue to build the expression as before. The entire expression is:

tonumber(LookupDatabaseTable( 'qatest.dbo.DEPT', 'DEPTNO', 'DEPT_RANKING', tostring( "DEPARTMENT" ) ) )

When you specify the arguments for the LookupDatabaseTable method, you specify the following arguments:

1. fully qualified name of the lookup table

2. unqualified name of the input lookup key column

3. unqualified name of the lookup column that contains the output value

4. key value, the search key

When the method is validated in the Connect CDC Director, the Expression Handler only ensures that these arguments are type characters; the Connect CDC Director does not validate the names in the source catalog. Validation in the source catalog only occurs when the kernel loads the model. If any of these name was entered incorrectly, you will not know until the kernel is loaded and the lookup SQL statement is prepared in the server.