Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(2 Ratings)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Use a PowerCenter mapping variable in a SQL override
Solution

The following example creates a user defined persistent mapping variable that is used in a SQL override.

This mapping processes invoice numbers in continuous runs.

  1. In the Designer create a mapping variable $$Last_Invoice with an initial value and a datatype for evaluation.
    In this example it is an integer and initial value is 0.

  2. The SQL override in the source or Lookup SQL override will be like the following:

    SELECT INV_NO FROM ORDERS
    WHERE INV_NO > $$Last_Invoice

  3. Create an Expression transformation with a port inv_no_in and a variable port.

  4. Link the inv_no from the Source Qualifier to inv_no_in in the Expression transformation.

  5. For the variable port, enter the following expression:

    SETVARIABLE($$Last_Invoice, inv_no_in)

After running the session, the PowerCenter Server saves the last value of the inv_no to the repository as the last evaluated current value for $$Last_Invoice .
The next time the session runs, the PowerCenter Server evaluates all references to $$Last_Invoice .

More Information

You should use SetVariable function only once for each mapping variable in a pipeline. When you create multiple partitions in a pipeline, the PowerCenter Server uses multiple threads to process that pipeline. If you use this function more than once for the same variable, the current value of a mapping variable may have in deterministic results.

Use SETVARIABLE in the following transformations:

  • Expression
  • Filter
  • Router
  • Update Strategy
Reference

For details on mapping variables, see "Mapping Parameters and Variables" in the Designer Guide.

Applies To
Product: PowerCenter
Problem Type:
User Type: Administrator
Project Phase:
Product Version: PowerCenter
Database:
Operating System:
Other Software:
Attachments
Last Modified Date:1/20/2016 2:26 AMID:17501
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)