SETVARIABLE
Posted on: 12/14/2011
Sets the current value of a mapping variable to a value you specify. Returns the specified value. The SETVARIABLE function executes only if a row is marked as insert or update. SETVARIABLE ignores all other row types and the current value remains unchanged.
At the end of a successful session, the PowerCenter Integration Service compares the final current value of the variable to the start value of the variable. Based on the aggregate type of the variable, it saves a final current value to the repository. Unless overridden, it uses the saved value as the initial value of the variable for the next session run.
Use the SETVARIABLE function only once for each mapping variable in a pipeline. The PowerCenter Integration Service processes variable functions as it encounters them in the mapping. The order in which the PowerCenter Integration Service encounters variable functions in the mapping may not be the same for every session run. This may cause inconsistent results when you use the same variable function multiple times in a mapping.
Use SETVARIABLE in the following transformations:
- Expression
- Filter
- Router
- Update Strategy
The PowerCenter Integration Service does not save the final value of a mapping variable to the repository when any of the following conditions are true:
- The session fails to complete.
- The session is configured for a test load.
- The session is a debug session.
- The session runs in debug mode and is configured to discard session output.
Syntax
SETVARIABLE( $$Variable, value )
|
Argument
|
Required/
Optional
|
Description
|
|
$$Variable
|
Required
|
Name of the mapping variable you want to set. Use with mapping variables with Max/Min aggregation type.
|
|
value
|
Required
|
The value you want to set the current value of the variable to. You can enter any valid transformation expression that evaluates to a datatype compatible with the datatype of the variable.
|
Return Value
Current value of the variable.
When value is NULL, the PowerCenter Integration Service returns the current value of $$Variable.
Examples
The following expression sets a mapping variable $$Time to the system date at the time the PowerCenter Integration Service evaluates the row and returns the system date to the SET_$$TIME port:
SETVARIABLE ($$Time, SYSDATE)
|
TRANSACTION
|
ITEMS
|
SET_$$TIME
|
|
0100002
|
534.23
|
10/10/2000 01:34:33
|
|
0100003
|
699.01
|
10/10/2000 01:34:34
|
|
0100004
|
97.50
|
10/10/2000 01:34:35
|
|
0100005
|
116.43
|
10/10/2000 01:34:36
|
|
0100006
|
323.95
|
10/10/2000 01:34:37
|
At the end of the session, the PowerCenter Integration Service saves 10/10/2000 01:34:37 to the repository as the last evaluated current value for $$Timestamp.
The next time the session runs, the PowerCenter Integration Service evaluates all references to $$Timestamp to 10/10/2000 01:34:37.
The following expression sets the mapping variable $$Timestamp to the timestamp associated with the row and returns the timestamp to the SET_$$TIMESTAMP port:
SETVARIABLE ($$Time, TIMESTAMP)
|
TRANSACTION
|
TIMESTAMP
|
TOTAL
|
SET_$$TIMESTAMP
|
|
0100002
|
10/01/2000 12:01:01
|
534.23
|
10/01/2000 12:01:01
|
|
0100003
|
10/01/2000 12:10:22
|
699.01
|
10/01/2000 12:10:22
|
|
0100004
|
10/01/2000 12:16:45
|
97.50
|
10/01/2000 12:16:45
|
|
0100005
|
10/01/2000 12:23:10
|
116.43
|
10/01/2000 12:23:10
|
|
0100006
|
10/01/2000 12:40:31
|
323.95
|
10/01/2000 12:40:31
|
At the end of the session, the PowerCenter Integration Service saves 10/01/2000 12:40:31 to the repository as the last evaluated current value for $$Timestamp.
The next time the session runs, the PowerCenter Integration Service evaluates the initial value of $$Timestamp to
10/01/2000 12:40:31.
At the end of the session, the PowerCenter Integration Service saves 10/01/2000 12:40:31 to the repository as the last evaluated current value for $$Timestamp.
Informatica Reference 