Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • PowerCenter 10.4.0
  • All Products

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

Back Next

SETVARIABLE

SETVARIABLE

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
 )
The following table describes the arguments for this command:
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
TOTAL
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 $$Time. The next time the session runs, the
PowerCenter Integration Service
 evaluates all references to $$Time 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.
0 COMMENTS
We’d like to hear from you!