Expression Syntax
Although the transformation language is based on standard SQL, there are difference between the two languages. For example, SQL supports the keywords ALL and DISTINCT for aggregate functions, but the transformation language does not. On the other hand, the transformation language supports an optional filter condition for aggregate functions, while SQL does not.
You can create an expression that is as simple as a port (such as ORDERS), a pre-defined workflow variable (such as $Start.Status), or a numeric literal (such as 10). You can also write complex expressions that include functions nested within functions, or combine different columns using the transformation language operators.
Expression Components
Expressions can consist of any combination of the following components:
- •Ports (input, input/output, variable)
- •String literals, numeric literals
- •Constants
- •Functions
- •Built-in and local variables
- •Mapping parameters and mapping variables
- •Pre-defined workflow variables
- •User-defined workflow variables
- •Operators
- •Return values
Ports and Return Values
When you write an expression that includes a port or return value from an unconnected transformation, use the reference qualifiers in the following table:
Reference Qualifier | Description |
|---|
:EXT | Required when you write an expression that includes a return value from an External Procedure transformation. The general syntax is: :EXT.external_procedure_transformation(argument1, argument2, ...) |
:LKP | Required when you create an expression that includes the return value from an unconnected Lookup transformation. The general syntax is: :LKP.lookup_transformation(argument1, argument2, ...) The arguments are the local ports used in the lookup condition. The order must match the order of the ports in the transformation. The datatypes for the local ports must match the datatype of the Lookup ports used in the lookup condition. |
:SD | Optional (PowerMart 3.5 expressions only). Qualifies a source table port in an expression. The general syntax is: :SD.source_table.column_name |
:SEQ | Required when you create an expression that includes a port in a Sequence Generator transformation. The general syntax is: :SEQ.sequence_generator_transformation.CURRVAL |
:SP | Required when you write an expression that includes the return value from an unconnected Stored Procedure transformation. The general syntax is: :SP.stored_procedure_transformation( argument1, argument2, [, PROC_RESULT]) The arguments must match the arguments in the unconnected Stored Procedure transformation. |
:TD | Required when you reference a target table in a PowerMart 3.5 LOOKUP function. The general syntax is: LOOKUP(:TD.SALES.ITEM_NAME, :TD.SALES.ITEM_ID, 10, :TD.SALES.PRICE, 15.99) |
String and Numeric Literals
You can include numeric or string literals.
Be sure to enclose string literals within single quotation marks. For example:
'Alice Davis'
String literals are case sensitive and can contain any character except a single quotation mark. For example, the following string is not allowed:
'Joan's car'
To return a string containing a single quote, use the CHR function:
'Joan' || CHR(39) || 's car'
Do not use single quotation marks with numeric literals. Just enter the number you want to include. For example:
.05
or
$$Sales_Tax
Rules and Guidelines for Expression Syntax
Use the following rules and guidelines when you write expressions:
- •You cannot include both single-level and nested aggregate functions in an Aggregator transformation.
- •If you need to create both single-level and nested functions, create separate Aggregator transformations.
- •You cannot use strings in numeric expressions.
For example, the expression 1 + '1' is not valid because you can only perform addition on numeric datatypes. You cannot add an integer and a string.
- •You cannot use strings as numeric parameters.
For example, the expression SUBSTR(TEXT_VAL, '1', 10) is not valid because the SUBSTR function requires an integer value, not a string, as the start position.
- •You cannot mix datatypes when using comparison operators.
For example, the expression 123.4 = '123.4' is not valid because it compares a decimal value with a string.
- •You can pass a value from a port, literal string or number, variable, Lookup transformation, Stored Procedure transformation, External Procedure transformation, or the results of another expression.
- •Use the ports tab in the Expression Editor to enter a port name into an expression. If you rename a port in a connected transformation, the Designer propagates the name change to expressions in the transformation.
- •Separate each argument in a function with a comma.
- •Except for literals, the transformation language is not case sensitive.
- •Except for literals, the Designer and PowerCenter Integration Service ignore spaces.
- •The colon (:), comma (,), and period (.) have special meaning and should be used only to specify syntax.
- •The Data Integration Service treats a dash (-) as a minus operator.
- •If you pass a literal value to a function, enclose literal strings within single quotation marks. Do not use quotation marks for literal numbers. The Data Integration Service treats any string value enclosed in single quotation marks as a character string.
- •When you pass a mapping parameter or variable or a workflow variable to a function within an expression, do not use quotation marks to designate mapping parameters or variables or workflow variables.
- •Do not use quotation marks to designate ports.
- •You can nest multiple functions within an expression except aggregate functions, which allow only one nested aggregate function. The Data Integration Service evaluates the expression starting with the innermost function.