Rules for column maps differ between Compare and Move or Archive. These differences are discussed in the following section.
Compare automatically matches every column in the Source 1 table to the Source 2 column that has the same name and compatible data type. Click a Source 1 grid column to select a column name from the list of unmatched Source 1 columns. Select the blank space to exclude a column from the comparison. No other editing of Source 1 specifications is possible.
The Column Map Editor lists the pairs of columns that are mapped. You can edit Source specifications in the following ways:
You can map a Source column to more than one Destination column if the data types are compatible.
Right-click and select List Columns from the shortcut menu (or select List Columns from the Tools menu) to list columns from the source table. (The List Columns command is enabled when the Column Map uses Move/Archive validation rules.)

Fully qualified name of the source table.
Name of each column in the source table. To select a column, click and drag the column name from the List Columns dialog to the Source Column grid cell in the Column Map Editor. The status is updated automatically.
To change the column display, select commands from the View menu in the List Columns dialog, as follows:
Data type for each column in the source table.
Type over source column values or right-click to select commands to Clear, Find, or Replace source column values. (If you clear a Source Column grid cell, the corresponding destination column value is not affected.)
A source column value in bold type can be viewed and edited from the Source Column dialog only. To open the dialog, double-click the value or use the Expand Source Column command. For more information, see Expand Source Column.
You can use any of several methods to map values to Destination Columns. Specify:
| Value | Description | |
|---|---|---|
| Column Name | An explicit column name (column names are case‑insensitive). | |
| NULL | NULL. The destination column must be nullable. | |
| Numeric Constant | A numeric constant. The constant value must fit into the destination column as defined by its data type, precision, and scale. | |
| Boolean Constant | A Boolean constant (TRUE or FALSE). | |
| Special Register | A special
register:
|
|
| String Literal | A string
literal, which is enclosed in single quotation marks. The destination
column must contain character data. Example: 'CA' or '90210'. |
|
| Hexadecimal Literal | A hexadecimal
literal. Example: X‘1234567890ABCDEF' or 0X1234567890ABCDEF |
|
| Date/Time Literal | A date/time
literal, which is enclosed in single quotation marks. Separate the
date and time with a space. To format the date/time with a decimal
fraction, place a period after the time, followed by the fraction.
The date format is determined by the settings in Regional
Options on the Control Panel of your computer. Note: For
Oracle Timestamp with Time Zone columns, you must specify the time
zone suffix last.
|
|
| Substring Function | A Substring Function to use a portion of a source column value. | |
| Random Function | A Random Function to generate a random value. | |
| Sequential Function | A Sequential Function to define an incremental sequential value. | |
| Identity or Serial Function | An Identity or Serial Function to direct the DBMS to supply a sequential value (integer) for a destination column. | |
| Oracle Sequence Function | An Oracle Sequence Function to assign a value to a destination column using an Oracle Sequence. | |
| Propagate Primary or Foreign Key Value Function | A Propagate Function to assign a value to a primary or foreign key column and propagate that value to all related tables. | |
| Concatenated Expressions | A Concatenated Expression to obtain a derived value. | |
| Numeric Expressions | A Numeric Expression to obtain a derived numeric value. | |
| Column Map Procedure | A Column Map Procedure to obtain site-specific values. See Column Map Procedures. | |
| Exit Routine | An Exit Routine to obtain site-specific values. See Exit routines for column maps. | |
These functions require an Optim Data Privacy license. For detailed information, see Data Privacy Functions. To use these functions to map values to Destination Columns, specify:
| Value | Description |
|---|---|
| Lookup Functions | A Lookup Function to obtain the value for one or more destination columns from a lookup table. |
| Random Lookup Function | A Random Lookup Function to generate a random value for one or more destination columns from a lookup table. |
| Hash Lookup Function | A Hash Lookup Function to obtain the value for one or more destination columns from a lookup table, according to a hashed value derived from a source column. |
| Shuffle Function | A Shuffle Function to replace a source value with another value from the column. |
| Transformation Library Functions | The Transformation Library Functions to mask personal data such as a social security number, credit card number, or email address. |
| Age Function | An Age Function to age the source column value. |
| Currency Function | A Currency Function to convert source column currency values. |
Use the Source Column dialog to enter long source column values or functions up to 999 characters. The Source Column dialog is available only for Column Maps using Move/Archive validation and is not available for columns that contain a reference to a Column Map Procedure. You can enter a reference to Column Map Procedure in the Source Column dialog, but the dialog will not be available for the column after the value is defined.
To open the Source Column dialog, right-click the source column cell and select Expand Source Column from the shortcut menu (or place the cursor in the cell and select Expand Source Column from the Tools menu).

If the source column value includes carriage return/line feed pairs, the value is displayed in bold type in the Column Map and can be viewed and edited in the Source Column dialog only. Carriage return/line feed pairs are removed from a reference to a Column Map Procedure. Carriage return/line feed pairs are not displayed in the value and are created by using the Enter key to make a line break in the Source Columndialog (they might also be present in an imported Column Map). To open the Source Column dialog for a value in bold type, double-click the value or use the Expand Source Column command.
Enter text in the Source Text area. Click OK to exit the dialog and validate the entry (if the text is not valid, the dialog remains open and display error messages). Click Cancel to exit the dialog and clear the entry, retaining the original source column value.
Right-click the Source Text area to display the following options:
The Substring Function returns a substring of the contents of the named column. Here is the syntax:
SUBSTR(columnname, start, [length])
If the PHONE_NUMBER column is defined as CHAR(10), you can use the Substring Function to map the area code. To obtain a substring of the first three positions of the phone number (area code) for the destination column, specify:
SUBSTR(PHONE_NUMBER, 1, 3)
The Random Function returns a number that is selected at random within the range indicated by the low and high values. Here is the syntax:
RAND(low, high)
You can use the Random Function to mask or change sales data for a test database. Assume the YTD_SALES column is defined as DECIMAL(7,2). The maximum number of digits that can precede the decimal is 5; the possible range for this column is -99999 to 99999. To create test data within a range from 1000 (low) to 89999 (high), specify:
RAND(1000, 89999)
In this example, the function returns random sales values within the range you specified from 1000.00 to 89999.99.
The Sequential Function returns a number that is incremented sequentially. Here is the syntax:
SEQ(start, step)
You can use the Sequential Function to change customer data for a test database. Assume that the CUST_ID column is defined as CHAR(5). To increment by 50, starting at 1, specify:
SEQ(1, 50)
In this example, the function returns CUST_ID values starting at '00001' and increments by 50 to generate '00051', '00101', and so on. When the result exceeds '99951', the function resets to the start value of 1.
You can use the Sequential Function in a Column Map to mask sales data for a test database. Assume that the YTD_SALES column is defined as DECIMAL(7,2). To increment by 100 starting at 1000, specify:
SEQ(1000, 100)
In this example, the function returns YTD_SALES values starting at 1000 and increments by 100 to generate 1100, 1200, and so on. When the result exceeds 99999, the function resets to the start value of 1000.
Assume that the SALESMAN_ID column is defined as CHAR(6). To insert values beginning with ‘NJ,' followed by a number starting at 50 and incremented by 10, use the function in a concatenated expression:
'NJ'||SEQ(50, 10)
In this example, the function returns SALESMAN_ID values starting at 'NJ50 ' and increments by 10 to generate 'NJ60 ', 'NJ70 ', and so on. When the result exceeds 'NJ9990', the function resets to the start value.
The Identity and Serial Functions direct the DBMS to supply a sequential value (integer) for a destination column. Here is the syntax for these functions:
IDENTITY( )
SERIAL ( )
The Oracle Sequence Function assigns a value to the destination column using an Oracle Sequence. Here is the syntax:
schema.seqname.NEXTVAL [INCL_UPD]
To assign a sequential value to increment customer numbers, where the name of the Oracle Sequence is schema.numeven, specify:
schema.numeven.NEXTVAL
To expand the first example and update an existing sequence value, specify:
schema.numeven.NEXTVAL(INCL_UPD)
The Propagate Function assigns a value to a primary key or foreign key column and propagates that value to all related tables. Here is the syntax:
PROP( { value [, columnname| ]
EXIT exitname |
PROC { LOCAL | identifier.name }
} )
The column name is required only if no source column matches the destination column in both name and data type. If not specified, the name of the destination column is used.
Before you do an Insert, Load or Convert Process, you can review the Column Map to verify how the Propagate Function is used in the process.
You can generate a random number, assign it to the default destination column, and propagate the number in the destination columns of related tables. To generate a value between 10000 and 99999, insert it into the mapped destination column and propagate it to the destination columns of related tables, specify:
PROP(RAND(10000, 99999))
You can do the same function as in Example 1 when the source and destination column names do not match. To include the name of the source column (CUST_NUMBER) in the Propagate Function, specify:
PROP(RAND(10000, 99999), CUST_NUMBER)
You can use Oracle Sequence to generate the value for the destination column and propagate that value in destination columns of the related tables. To propagate the Oracle Sequence named, schema.numeven, specify:
PROP(schema.numeven.NEXTVAL)
You can use concatenation to combine column values or combine a column value with another value by using a concatenation operator (CONCAT, ||, or +). A concatenated expression can include character values or binary values, but not both.
Assume that the CUSTOMERS table stores an address in two columns: ADDRESS1 and ADDRESS2. The SHIP_TO table stores an address in one column: ADDRESS. You can use a concatenated expression to combine address information from two columns in one table to one column in another.
To combine the address, specify one of the following:
| CUSTOMERS Table | SHIP_TO Table |
|---|---|
| ADDRESS1 || ADDRESS2 | ADDRESS |
| ADDRESS1 CONCAT ADDRESS2 | ADDRESS |
| ADDRESS1 + ADDRESS2 | ADDRESS |
You can use a numeric expression to specify a value in the source column whenever the data types for the corresponding source and destination columns are compatible. An arithmetic expression consists of:
operand1 operator operand2
Each operand must be a numeric column or a numeric constant. The operator specifies whether to add (+), subtract (-), divide (/), or multiply (*).
To increase the value in a column UNIT_PRICE defined as DECIMAL(5,2) by 10 percent, specify:
1.1 * UNIT_PRICE
To divide the value in an INTEGER column ON_HAND_INVENTORY in half, specify:
ON_HAND_INVENTORY / 2.