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

SETCOUNTVARIABLE

SETCOUNTVARIABLE

Counts the rows evaluated by the function and increments the current value of a mapping variable based on the count. Increases the current value by one for each row marked for insertion. Decreases the current value by one for each row marked for deletion. Keeps the current value the same for each row marked for update or reject. Returns the new current value.
At the end of a successful session, the
PowerCenter Integration Service
saves the last 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 determines the total count for all partitions and saves the total to the repository. Unless overridden, it uses the saved value as the initial value of the variable for the next time you use this session.
Use the SETCOUNTVARIABLE 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 SETCOUNTVARIABLE with mapping variables with a Count aggregation type. Use SETCOUNTVARIABLE 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 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

SETCOUNTVARIABLE( $$
Variable
)
The following table describes the argument for this command:
Argument
Required/
Optional
Description
$$
Variable
Required
Name of the mapping variable you want to set. Use mapping variables with a count aggregation type.

Return Value

The current value of the variable.

Example

You have a mapping that updates a slowly changing dimension table containing distributor information. The following expression counts the number of current distributors with the mapping variable $$CurrentDistributors and returns the current value to the CUR_DIST port. It increases the count by one for each inserted row, decreases the count for each deleted row, and keeps the count the same for all updated or rejected rows. The initial value of $$CurrentDistributors from the previous session run is 23.
SETCOUNTVARIABLE ($$CurrentDistributors)
(row marked for...)
DIST_ID
DISTRIBUTOR
CUR_DIST
(update)
000015
MSD Inc.
23
(insert)
000024
Darkroom Co.
24
(insert)
000025
Howard's Supply
25
(update)
000003
JNR Ltd.
25
(delete)
000024
Darkroom Co.
24
(insert)
000026
Supply.com
25
At the end of the session, the
PowerCenter Integration Service
saves ‘25’ to the repository as the current value for $$CurrentDistributors. The next time the session runs, the Integration Service evaluates the initial value to $$CurrentDistributors to ‘25’.
The
PowerCenter Integration Service
saves the same value for $$CurrentDistributors to the repository for sessions with multiple partitions as for sessions with a single partition.