How to Create New Column CaptureExpression for File Location

Question

How can I combine two columns on source (skumodel and serial_num) into one combined value column on target (model_serial)?

Environment

HVR 5

Answer

Since your target is a file location, you cannot just simply add the column as an /Extra column with an /IntegrateExpression because the /IntegrateExpression would use the target DBMS functions. This can be achieved with a small workaround using /CaptureLocation by performing the following steps:

1. Double-click the table's name in the GUI.

2. Add the column manually to the table:

   

3. Define the ColumnProperties action on your source location or location group as shown below:

   Location SRC: ColumnProperties /Name=model_serial /Absent /CaptureExpression="trim({skumodel})||'-'||trim({serial_num})" /Datatype=varchar2 /Length=201

4. Reinitialize your jobs with Scripts and Jobs.

   NOTE: Adding a /CaptureExpression to your replication can create an overhead because of the expensive round-trip calls made to the database in every captured row.