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
  • Data Integration
  • All Products

Table of Contents

Search

  1. Preface
  2. Function reference
  3. Constants
  4. Operators
  5. Dates
  6. Functions
  7. System variables
  8. Datatype reference

Function Reference

Function Reference

Back Next

SETVARIABLE

SETVARIABLE

Sets the current value of an in-out parameter to a value you specify. Returns the specified value.
During task execution,
Data Integration
 compares the final current value of the in-out parameter to the start value. Based on the aggregate type of the in-out parameter, it saves a final current value in the job details. Unless overridden, it uses the saved value as the initial value of the in-out parameter for the next task run.
Use the SETVARIABLE function only once for each in-out parameter in a pipeline.
Data Integration
 processes variable functions as it encounters them in the mapping. The order in which
Data Integration
 encounters variable functions in the mapping may not be the same for every task session. This could cause inconsistent results if you use the same variable function multiple times in a mapping.
You can use SETVARIABLE in the Expression transformation.

Syntax

SETVARIABLE( $$Variable, value )
Argument
Required/
Optional
Description
$$
Variable
Required
Name of the in-out parameter you want to set. Use with in-out parameters configured with the Max or Min aggregation type.
value
Required
The value to set as the current value of the in-out parameter. You can use a valid expression that evaluates to a datatype compatible with the datatype of the parameter.

Return Value

Current value of the in-out parameter.
When
value
 is NULL,
Data Integration
 returns the current value of
$$Variable
.

Examples

The following expression sets an in-out parameter $$Time to the system date at the time
Data Integration
 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/2016 01:34:33
0100003
699.01  
10/10/2016 01:34:34
0100004
97.50  
10/10/2016 01:34:35
0100005
116.43  
10/10/2016 01:34:36
0100006
323.95  
10/10/2016 01:34:37
At the end of the session,
Data Integration
 saves 10/10/2016 01:34:37 in the job details as the last evaluated current value for $$Time. The next time the task runs,
Data Integration
 evaluates all references to $$Time to 10/10/2016 01:34:37.
The following expression sets the in-out parameter $$Timestamp to the timestamp associated with the row and returns the timestamp to the SET_$$TIMESTAMP port: 
SETVARIABLE ($$Time, TIMESTAMP)
TRANSACTION
TIMESTAMP
 TOTAL
SET_$$TIME
 
0100002
10/01/2016 12:01:01
534.23  
10/01/2016 12:01:01
0100003
10/01/2016 12:10:22
699.01  
10/01/2016 12:10:22
0100004
10/01/2016 12:16:45
97.50  
10/01/2016 12:16:45
0100005
10/01/2016 12:23:10
116.43  
10/01/2016 12:23:10
0100006
10/01/2016 12:40:31
323.95  
10/01/2016 12:40:31
At the end of the session,
Data Integration
 saves 10/01/2016 12:40:31 in the job details as the last evaluated current value for $$Timestamp.
The next time the session runs,
Data Integration
 sets the initial value of $$Timestamp to 10/01/2016 12:40:31.
Back to Top
2 COMMENTS
Back Next
We’d like to hear from you!
Tim Shannon - June 24, 2022

What is the point of setvariable and setmaxvariable if they do they work exactly the same?

Informatica Documentation Team - June 25, 2022

Hi Tim, 

They are actually different. SETMAXVARIABLE sets the current value of an in-out parameter to the higher of the current value of the parameter or the value you specify - so if the current value is higher, it returns the current value, but if the value you specify is higher, it returns the value you specify. SETVARIABLE sets the current value of an in-out parameter to a value you specify.

Kip Simonsen - March 03, 2025

I'm here to echo Tim Shannon's question: the documentation for both SetVariable and SetMaxVariable indicate that they accomplish the same thing, with the exception that SetVariable can do what both SetMaxVariable and SetMinVariable do. I'm facing an issue where I need the in-out parameter to be not constrained by the aggregation type set in the settings, but it looks like that overwrites everything. I want to save whatever the value of SetVariable produces in the expression, but at the end of the mapping task, all signs point to the aggregation type on the in-out parameter trumping the SetVariable value, should it fall not in the min/max of the aggregation type. How can I just set the variable using the SetVariable function, ignoring the aggregation type? Seems redundant that you have functions for it, but SetVariable + the aggregation type accomplishes the same purpose.

Informatica Documentation Team - March 04, 2025

Hi Kip,

Thanks for reaching out. We're currently checking with our development team on this and will get back to you shortly.

Informatica Documentation Team - March 10, 2025

Hi Kip,

We received the following answer from our development and QA team:

"The SETVARIABLE function accepts two parameters – value and aggregation. There is no other way to use this function. For the use case you mentioned, you should choose the count aggregation method, but note that count only works with numeric values. Count doesn’t work with timestamp or string values and will throw an error.

With a numeric value, the value must be positive. If a negative numeric value is passed when you use count, this will also throw an error."

If you need further help with your particular use case, please reach out to Informatica Global Customer Support.

Since there seems to be some confusion regarding the difference between the SETVARIABLE and SETMAXVARIABLE functions, we'll also add an example to the documentation in an upcoming release.

Thanks again for your query!