Mapping函数

ADD_TO_DATE：YY，MONTH，DD，HH，MI，SS

COUNT

Returns the number of rows that have non-null values in a group. Optionally, you can include the asterisk (*) argument to count all input values in a transformation.

COUNT is one of several aggregate functions. You use aggregate functions in Aggregator transformations only. You can nest only one other aggregate function within COUNT. You can apply a condition to filter rows before counting them. CUME

Returns a running total. A running total means CUME returns a total each time it adds a value. You can add a condition to filter rows out of the row set before calculating the running total.

Use CUME and similar functions (such as MOVINGAVG and MOVINGSUM) to simplify reporting by calculating running values.

DATE_DIFF

Example

The following expressions return the number of hours between the DATE_PROMISED and DATE_SHIPPED ports:

DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'HH' ) DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'HH12' ) DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'HH24' )

DECODE

DECODE( TRUE,

Var1 = 22, 'Variable 1 was 22!', Var2 = 49, 'Variable 2 was 49!',

Var1 < 23, 'Variable 1 was less than 23.', Var2 > 30, 'Variable 2 was more than 30.', 'Variables were out of desired ranges.')

Var1 21 22 23 24 25

ERROR

Var2 47 49 49 27 50

RETURN VALUE

Variable 1 was less than 23. Variable 1 was 22! Variable 2 was 49!

Variables were out of desired ranges. Variable 2 was more than 30.

Causes the PowerCenter Server to skip a row and issue an error message, which you define. The error message displays in the session log file. The PowerCenter Server does not write these skipped rows to the session reject file.

You can use ERROR in Expression transformations to validate data.

Generally, you use ERROR within an IIF or DECODE function to set rules for skipping rows.

You can use the ERROR function for both input and output port default values. You might use ERROR for input ports to keep null values from passing into a transformation.

Use ERROR for output ports to handle any kind of transformation error, including ERROR function calls within an expression. When you use the ERROR function in an expression and in the output port default value, the PowerCenter Server skips the row and logs both the error message from the expression and the error message from the default value. If you want to ensure the PowerCenter Server skips rows that produce an error, assign ERROR as the default value.

If you use an output default value other than ERROR, the default value overrides the ERROR function in an expression. For example, you use the ERROR function in an expression, and you assign the default value, '1234', to the output port. Each time the PowerCenter Server encounters the ERROR function in the expression, it overrides the error with the value '1234' and passes '1234' to the next transformation. It does not skip the row, and it does not log an error in the session log.

IIF( SALARY < 0, ERROR ('Error. Negative salary found. Row skipped.', EMP_SALARY ) SALARY 10000 10000

-15000 'Error. Negative salary found. Row skipped.' NULL

NULL

RETURN VALUE

150000 150000 1005

FIRST

1005

ITEM_NAME

Flashlight

Navigation Compass Regulator System Flashlight

Depth/Pressure Gauge Flashlight

ITEM_PRICE 35.00 8.05 150.00 29.00 88.00 31.00

INSTR

Returns the position of a character set in a string, counting from left to right. Argument Meaning Required. The string must be a character string. Passes the value you want to evaluate. You can enter any valid transformation expression. The results of the expression must be a character string; if not, INSTR converts the value to a string before evaluating it. string Required. Any value. The search value is case-sensitive. The set of characters you want to search for. The search_value must match a part of the string exactly. For search_value example, if you write INSTR('Alfred Pope', 'Alfred Smith') the function returns zero. You can enter any valid transformation expression. If you want to search for a character string, enclose the characters you want to search for in single quotation marks, for example 'abc'. Optional. Must be an integer value. The position in the string where you want to start the search. You can enter any valid transformation expression. The default value is 1, meaning that INSTR starts the search at the first character in the string. If the start position is zero, INSTR searches from the first character in the string. If the start position is a positive number, INSTR locates the start position by counting from the beginning of the string. If the start position is a negative number, INSTR locates the start position by counting from the end of the string. If you omit this argument, the function uses the default value of 1. start Optional. A positive integer greater than zero. You can enter any valid transformation expression. If the search value appears more than once in the string, you can specify which occurrence you want to search for. For example, you would enter 2 to search for the second occurrence from the start position. occurrence If you omit this argument, the function uses the default value of 1, meaning that INSTR searches for the first occurrence of the search value. If you pass a decimal, the PowerCenter Server rounds it to the nearest integer value. If you pass a negative integer or zero, the mapping fails when you run a workflow.

ISNULL

Return Value

TRUE (1) if the value is NULL. FALSE (0) if the value is not NULL.

IS_SPACES

TRUE (1) if the row consists entirely of spaces. FALSE (0) if the row contains data.