The Transformation Language Overview
PowerCenter provides a transformation language that includes SQL-like functions to transform source data. Use these functions to write expressions and create functions called user-defined functions.
User-defined functions reuse expression logic and build complex expressions. You can include them in other user-defined functions or in expressions. User-defined functions follow the same guidelines as expressions. They use the same syntax and can use the same transformation language components.
Expressions modify data or test whether data matches conditions. For example, you might use the AVG function to calculate the average salary of all the employees, or the SUM function to calculate the total sales for a specific branch.
You can create a simple expression that only contains a port, such as ORDERS, or a numeric literal, such as 10. You can also write complex expressions that include functions nested within functions, or combine different ports using the transformation language operators.
Transformation Language Components
The transformation language includes the following components to create simple or complex transformation expressions:
- •Functions. Over 100 SQL-like functions allow you to change data in a mapping.
- •Operators. Use transformation operators to create transformation expressions to perform mathematical computations, combine data, or compare data.
- •Constants. Use built-in constants to reference values that remain constant, such as TRUE.
- •Mapping parameters and variables. Create mapping parameters for use within a mapping or mapplet to reference values that remain constant throughout a session, such as a state sales tax rate. Create mapping variables in mapplets or mappings to write expressions referencing values that change from session to session.
- •Workflow variables. Create workflow variables for use within a workflow to write expressions referencing values that change from workflow to workflow.
- •Built-in and local variables. Use built-in variables to write expressions that reference values that vary, such as the system date. You can also create local variables in transformations.
- •Return values. You can also write expressions that include the return values Lookup transformations.
Internationalization and the Transformation Language
Transformation language functions can handle character data in either ASCII or Unicode data movement mode. Use Unicode mode to handle multibyte character data. The return values of the following functions and transformations depend on the code page of the Data Integration Service and the data movement mode:
- •INITCAP
- •LOWER
- •UPPER
- •MIN (Date)
- •MIN (Number)
- •MIN (String)
- •MAX (Date)
- •MAX (Number)
- •MAX (String)
- •Any function that uses conditional statements to compare strings, such as IIF and DECODE
MIN and MAX also return values based on the sort order associated with the Data Integration Service code page.
When you validate an invalid expression in the Expression Editor, a dialog box displays the expression with an error indicator, “>>>>”. This indicator appears to the left of and points to the part of the expression containing the error. For example, if the expression a = b + c contains an error at c, the error message displays:
a = b + >>>> c
Transformation language functions that evaluate character data are character-oriented, not byte-oriented. For example, the LENGTH function returns the number of characters in a string, not the number of bytes. The LOWER function returns a string in lowercase based on the code page of the Data Integration Service.