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 as long as 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.)
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:
Overtype 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, enclosed in single quotes. The destination column must contain character data. | |
Hexadecimal Literal | A hexadecimal literal. | |
Date/Time Literal | A date/time
literal, enclosed in single quotes. 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 e-mail 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 Columndialog 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 may 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 will remain open and display error messages). Click Cancel to exit the dialog and clear the entry, retaining the original source column value.
The Substring Function returns a substring of the contents of the named column. The syntax is:
SUBSTR(columnname, start, [length])
The Random Function returns a number selected at random within the range indicated by the low and high values. The syntax is:
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 to the left of 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:
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. The syntax is:
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:
In this example, the function returns CUST_ID values starting at '00001' and increments by 50 to generate '00051', '00101', etc. 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:
In this example, the function returns YTD_SALES values starting at 1000 and increments by 100 to generate 1100, 1200, etc. 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:
In this example, the function returns SALESMAN_ID values starting at 'NJ50 ' and increments by 10 to generate 'NJ60 ', 'NJ70 ', etc. 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. The syntax for these functions is:
The Oracle Sequence Function assigns a value to the destination column using an Oracle Sequence. The syntax is:
schema.seqname.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. The syntax is:
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 executing 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:
Concatenation allows you to combine column values or combine a column value with another value, 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:
Each operand must be a numeric column or a numeric constant. The operator specifies whether to add ( + ), subtract ( - ), divide ( / ), or multiply ( * ).