Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Dates
  6. Functions
  7. Creating Custom Functions
  8. Custom Function API Reference
  9. POWERCENTERHELP

Transformation Language Reference

Transformation Language Reference

Rules and Guidelines for Expression Syntax

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
    PowerCenter 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
    PowerCenter 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
    PowerCenter Integration Service
    evaluates the expression starting with the innermost function.