Table of Contents

Search

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

Transformation Language Reference

Transformation Language Reference

Filter Conditions

Filter Conditions

Use a filter condition to limit the rows returned in a search.
A filter limits the rows returned in a search. You can apply a filter condition to all aggregate functions and to CUME, MOVINGAVG, and MOVINGSUM. The filter condition must evaluate to TRUE, FALSE, or NULL. If the filter condition evaluates to NULL or FALSE, the
PowerCenter Integration Service
does not select the row.
You can enter any valid transformation expression. For example, the following expression calculates the median salary for all employees who make more than $50,000:
MEDIAN( SALARY, SALARY > 50000 )
You can also use other numeric values as the filter condition. For example, you can enter the following as the complete syntax for the MEDIAN function, including a numeric port:
MEDIAN( PRICE, QUANTITY > 0 )
In all cases, the
PowerCenter Integration Service
rounds a decimal value to an integer (for example, 1.5 to 2, 1.2 to 1, 0.35 to 0) for the filter condition. If the value rounds to 0, the filter condition returns FALSE. If you do not want to round up a value, use the TRUNC function to truncate the value to an integer:
MEDIAN( PRICE, TRUNC( QUANTITY ) > 0 )
If you omit the filter condition, the function selects all rows in the port.