Informatica Reference

SETMINVARIABLE

Posted on: 12/14/2011

Sets the current value of a mapping variable to the lower of two values: the current value of the variable or the value you specify. Returns the new current value. The SETMINVARIABLE function executes only if a row is marked as insert. SETMINVARIABLE ignores all other row types and the current value remains unchanged.

At the end of a successful session, the PowerCenter Integration Service saves the final current value to the repository. When used with a session that contains multiple partitions, the PowerCenter Integration Service generates different current values for each partition. At the end of the session, it saves the lowest current value across all partitions to the repository. Unless overridden, it uses the saved value as the initial value of the variable for the next session run.

When used with a string mapping variable, SETMINVARIABLE returns the lower string based on the sort order selected for the session.

Use the SETMINVARIABLE 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 SETMINVARIABLE with mapping variables with a Min aggregation type. Use SETMINVARIABLE 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:

Syntax
SETMINVARIABLE( $$Variable, value )

Argument
Required/
Optional
Description
$$Variable
Required
Name of the mapping variable you want to set. Use with mapping variables with Min aggregation type.
value
Required
The value you want the PowerCenter Integration Service to compare against the current value of the variable. You can enter any valid transformation expression that evaluates to a datatype compatible with the datatype of the variable.

Return Value

The lower of two values: the current value of the variable or the value you specified. The return value is the new current value of the variable.

When value is NULL, the PowerCenter Integration Service returns the current value of $$Variable.

Example

The following expression compares the price of an item with a mapping variable $$MinPrice. It sets $$MinPrice to the lower of two values and returns the historically lowest item price to the MIN_PRICE port. The initial value of $$MinPrice from the previous session run is 22.50.

SETMINVARIABLE ($$MinPrice, PRICE)

DATE
PRICE 
MIN_PRICE
05/01/2000 09:00:00
23.50
22.50
05/01/2000 10:00:00
27.00
22.50
05/01/2000 11:00:00
26.75
22.50
05/01/2000 12:00:00
25.25
22.50
05/01/2000 13:00:00
22.00
22.00
05/01/2000 14:00:00
22.75
22.00
05/01/2000 15:00:00
23.00
22.00
05/01/2000 16:00:00
24.25
22.00
05/01/2000 17:00:00
24.00
22.00

At the end of the session, the PowerCenter Integration Service saves 22.00 to the repository as the minimum current value for $$MinPrice. The next time the session runs, the PowerCenter Integration Service evaluates the initial value to $$MinPrice to 22.00.

If the same session contains three partitions, the PowerCenter Integration Service evaluates $$MinPrice for each partition. Then, it saves the smallest value to the repository. For example, the last evaluated value for $$MinPrice in each partition is as follows:

Partition      Final Current Value for $$MinPrice
Partition 1      22.00
Partition 2      22.00
Partition 3      22.00

Advertisements
%d bloggers like this: