Preface
Function reference
- Function components
Constants
Operators
Dates
Functions
- Function overview
  - Aggregate functions
    - Filter conditions
  - Conversion functions
  - Data cleansing functions
  - Date functions
  - Encoding functions
  - Financial functions
  - Horizontal expansion functions
  - Numeric functions
  - Scientific functions
  - Special functions
  - String functions
  - Test functions
- Function quick reference
- %OPR_CONCAT%
- %OPR_CONCATDELIM%
- %OPR_IIF%
- %OPR_SUM%
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- AES_GCM_DECRYPT
- AES_GCM_ENCRYPT
- ASCII
- AVG
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COMPRESS
- CONCAT
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DECODE
- DECOMPRESS
- ENC_BASE64
- ERROR
- EXP
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- IS_DATE
- IS_NUMBER
- IS_SPACES
- ISNULL
- LAST
- LAST_DAY
- LEAST
- LENGTH
- LN
- LOG
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SET_DATE_PART
- SETCOUNTVARIABLE
- SETMAXVARIABLE
- SETMINVARIABLE
- SETVARIABLE
  - Differences between SETVARIABLE, SETMAXVARIABLE, and SETMINVARIABLE
- SHA256
- SIGN
- SIN
- SINH
- SOUNDEX
- SQRT
- STDDEV
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_FLOAT
- TO_INTEGER
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- VARIANCE
System variables
Datatype reference

Function Reference

Back Next

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