Default Values for Ports
All transformations use default values that determine how the Integration Service handles input null values and output transformation errors.
Input, output, and input/output ports have a system default value that you can sometimes override with a user-defined default value. Default values have different functions in different types of ports:
The following table shows the system default values for ports in connected transformations:
Port Type | Default Value | Integration Service Behavior | User-Defined Default Value Supported |
|---|
Input, Pass-through | NULL | Integration Service passes all input null values as NULL. | Input, Input/Output |
Output, Pass-through | ERROR | Integration Service calls the ERROR function for output port transformation errors. The Integration Service skips rows with errors and writes the input data and the error message in the log file. | Output |
Variable ports do not support default values. The Integration Service initializes variable ports according to the datatype.
You can override some of the default values to change the Integration Service behavior when it encounters null input values and output transformation errors.
User-Defined Default Values
You can override the system default values with user-defined default values for supported input, pass-through, and output ports within a connected transformation.
Use the following rules and guidelines to override the system default values for ports:
- •Input ports. You can enter user-defined default values for input ports if you do not want the Integration Service to treat null values as NULL. If NULL is passed to the input port, the Integration Service replaces NULL with the default value.
- •Output ports. You can enter user-defined default values for output ports if you do not want the Integration Service to skip the row or if you want the Integration Service to write a specific message with the skipped row to the log. If you define a default value in the output port, the Integration Service replaces the row with the default value when the output port has a transformation error.
- •Pass-through ports. You can enter user-defined default values for pass-through ports if you do not want the Integration Service to treat null values as NULL. You cannot enter user-defined default values for output transformation errors in a pass-through port.
Use the following options to enter user-defined default values:
- •Constant value. Use any constant (numeric or text), including NULL.
- •Constant expression. You can include a transformation function with constant parameters.
- •ERROR. Generate a transformation error. Write the row and a message in the mapping log or row error log.
- •ABORT. Abort the mapping.
Constant Values
You can enter any constant value as a default value. The constant value must match the port datatype.
For example, a default value for a numeric port must be a numeric constant. Some constant values include:
0
9999
NULL
'Unknown Value'
'Null input data'
Constant Expressions
A constant expression is any expression that uses transformation functions (except aggregate functions) to write constant expressions. You cannot use values from input, input/output, or variable ports in a constant expression.
Some valid constant expressions include:
500 * 1.75
TO_DATE('January 1, 1998, 12:05 AM','MONTH DD, YYYY, HH:MI AM')
ERROR ('Null not allowed')
ABORT('Null not allowed')
SESSSTARTTIME
You cannot use values from ports within the expression because the Integration Service assigns default values for the entire mapping when it initializes the mapping.
The following examples are not valid because they use values from ports:
AVG(IN_SALARY)
IN_PRICE * IN_QUANTITY
:LKP(LKP_DATES, DATE_SHIPPED)
ERROR and ABORT Functions
Use the ERROR and ABORT functions for input and output port default values, and input values for input/output ports. The Integration Service skips the row when it encounters the ERROR function. It aborts the mapping when it encounters the ABORT function.
User-Defined Default Input Values
You can enter a user-defined default input value if you do not want the Integration Service to treat null values as NULL.
To override null values, complete one of the following tasks:
- •Replace the null value with a constant value or constant expression.
- •Skip the null value with an ERROR function.
- •Abort the mapping with the ABORT function.
The following table summarizes how the Integration Service handles null input for input and input/output ports:
Default Value | Default Value Type | Description |
|---|
NULL (displays blank) | System | Integration Service passes NULL. |
Constant or Constant expression | User-Defined | Integration Service replaces the null value with the value of the constant or constant expression. |
ERROR | User-Defined | Integration Service treats this as a transformation error: - - Increases the transformation error count by 1.
- - Skips the row, and writes the error message to the log file or row error log.
The Integration Service does not write rows to the reject file. |
ABORT | User-Defined | The mapping aborts when the Integration Service encounters a null input value. The Integration Service does not increase the error count or write rows to the reject file. |
Replace Null Values
Use a constant value or expression to substitute a specified value for null values in a port.
For example, if an input string port is called DEPT_NAME and you want to replace null values with the string ‘UNKNOWN DEPT’, you could set the default value to ‘UNKNOWN DEPT’. Depending on the transformation, the Integration Service passes ‘UNKNOWN DEPT’ to an expression or variable within the transformation or to the next transformation in the data flow.
For example, the Integration Service replaces all null values in a port with the string ‘UNKNOWN DEPT.’
DEPT_NAME REPLACED VALUE
Housewares Housewares
NULL UNKNOWN DEPT
Produce Produce
Skip Null Records
Use the ERROR function as the default value when you do not want null values to pass into a transformation. For example, you might want to skip a row when the input value of DEPT_NAME is NULL. You could use the following expression as the default value:
ERROR('Error. DEPT is NULL')
When you use the ERROR function as a default value, the Integration Service skips the row with the null value. The Integration Service writes all rows skipped by the ERROR function into the log file. It does not write these rows to the reject file.
DEPT_NAME RETURN VALUE
Housewares Housewares
NULL 'Error. DEPT is NULL' (Row is skipped)
Produce Produce
The following log shows where the Integration Service skips the row with the null value:
TE_11019 Port [DEPT_NAME]: Default value is: ERROR(<<Transformation Error>> [error]: Error. DEPT is NULL
... error('Error. DEPT is NULL')
).
CMN_1053 EXPTRANS: : ERROR: NULL input column DEPT_NAME: Current Input data:
CMN_1053 Input row from SRCTRANS: Rowdata: ( RowType=4 Src Rowid=2 Targ Rowid=2
DEPT_ID (DEPT_ID:Int:): "2"
DEPT_NAME (DEPT_NAME:Char.25:): "NULL"
MANAGER_ID (MANAGER_ID:Int:): "1"
)
Abort the Mapping
Use the ABORT function to abort a mapping when the Integration Service encounters null input values.
Default Value Validation
The Developer tool validates default values as you enter them.
The Developer tool validates default values when you save a mapping. If you enter a default value that is not valid, the Developer tool marks the mapping as not valid.
User-Defined Default Output Values
You can create user-defined default values to override the system default values for output ports.
You can enter user-defined default values for output ports if you do not want the Integration Service to skip rows with errors or if you want the Integration Service to write a specific message with the skipped row to the log. You can enter default values to complete the following functions when the Integration Service encounters output transformation errors:
- •Replace the error with a constant value or constant expression. The Integration Service does not skip the row.
- •Abort the mapping with the ABORT function.
- •Write specific messages in the log for transformation errors.
You cannot enter user-defined default output values for input/output ports.
The following table summarizes how the Integration Service handles output port transformation errors and default values in transformations:
Default Value | Default Value Type | Description |
|---|
Transformation Error | System | When a transformation error occurs and you did not override the default value, the Integration Service performs the following tasks: - - Increases the transformation error count by 1.
- - Skips the row, and writes the error and input row to the session log file or row error log, depending on session configuration.
The Integration Service does not write the row to the reject file. |
Constant or Constant Expression | User-Defined | Integration Service replaces the error with the default value. The Integration Service does not increase the error count or write a message to the log. |
ABORT | User-Defined | Mapping aborts and the Integration Service writes a message to the log. The Integration Service does not increase the error count or write rows to the reject file. |
Replace Errors
If you do not want the Integration Service to skip a row when a transformation error occurs, use a constant or constant expression as the default value for an output port.
For example, if you have a numeric output port called NET_SALARY and you want to use the constant value ‘9999’ when a transformation error occurs, assign the default value 9999 to the NET_SALARY port. If there is any transformation error (such as dividing by zero) while computing the value of NET_SALARY, the Integration Service uses the default value 9999.
Abort the Mapping
Use the ABORT function to abort a session when the Integration Service encounters null input values.
Write Messages in the Mapping Log
You can configure a user-defined default value in an output port if you want the Integration Service to write a specific message in the mapping log for a skipped row. The system default is ERROR (‘transformation error’), and the Integration Service writes the message ‘transformation error’ in the log along with the skipped row. You can replace ‘transformation error’ if you want to write a different message.
ERROR Functions in Output Port Expressions
If you enter an expression that uses the ERROR function, the user-defined default value for the output port might override the ERROR function in the expression.
For example, you enter the following expression that instructs the Integration Service to use the value ‘Negative Sale’ when it encounters an error:
IIF( TOTAL_SALES>0, TOTAL_SALES, ERROR ('Negative Sale'))
The following examples show how user-defined default values may override the ERROR function in the expression:
- •Constant value or expression. The constant value or expression overrides the ERROR function in the output port expression.
For example, if you enter ‘0’ as the default value, the Integration Service overrides the ERROR function in the output port expression. It passes the value 0 when it encounters an error. It does not skip the row or write ‘Negative Sale’ in the log.
- •ABORT. The ABORT function overrides the ERROR function in the output port expression.
If you use the ABORT function as the default value, the Integration Service aborts the when a transformation error occurs. The ABORT function overrides the ERROR function in the output port expression.
- •ERROR. If you use the ERROR function as the default value, the Integration Service includes the following information in the log:
- - Error message from the default value
- - Error message indicated in the ERROR function in the output port expression
- - Skipped row
For example, you can override the default value with the following ERROR function:
ERROR('No default value')
The Integration Service skips the row, and includes both error messages in the log.
TE_7007 Transformation Evaluation Error; current row skipped...
TE_7007 [<<Transformation Error>> [error]: Negative Sale
... error('Negative Sale')
]
Sun Sep 20 13:57:28 1998
TE_11019 Port [OUT_SALES]: Default value is: ERROR(<<Transformation Error>> [error]: No default value
... error('No default value')
General Rules for Default Values
Use the following rules and guidelines when you create default values:
- •The default value must be either a NULL, a constant value, a constant expression, an ERROR function, or an ABORT function.
- •For input/output ports, the Integration Service uses default values to handle null input values. The output default value of input/output ports is always ERROR(‘Transformation Error’).
- •Variable ports do not use default values.
- •You can assign default values to group by ports in the Aggregator and Rank transformations.
- •Not all port types in all transformations allow user-defined default values. If a port does not allow user-defined default values, the default value field is disabled.
- •Not all transformations allow user-defined default values.
- •If a transformation is not connected to the mapping data flow, the Integration Service ignores user-defined default values.
- •If any input port is unconnected, its value is assumed to be NULL and the Integration Service uses the default value for that input port.
- •If an input port default value contains the ABORT function and the input value is NULL, the Integration Service immediately stops the mapping. Use the ABORT function as a default value to restrict null input values. The first null value in an input port stops the mapping
- •If an output port default value contains the ABORT function and any transformation error occurs for that port, the mapping immediately stops. Use the ABORT function as a default value to enforce strict rules for transformation errors. The first transformation error for this port stops the mapping
- •The ABORT function, constant values, and constant expressions override ERROR functions configured in output port expressions.
Default Value Validation
The Developer tool validates default values as you enter them.
The Developer tool validates default values when you save a mapping. If you enter a default value that is not valid, the Developer tool marks the mapping as not valid.