Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

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

Feedback

HOW TO: Set a key value to start at the last value in a key field in the target table using a PowerCenter mapping
Solution

To set a Sequence Generator to start at the last/highest value in the key field (SEQ_ID) in the target table add the following transformations to the mapping:

  • Unconnected Lookup Transformation
  • Sequence Generator Transformation
  • Expression Transformation

Lookup Transformation

Add a Lookup transformation from the target definition configured as follows:

  • The Lookup table name is the target table.
  • Add an input column, INPUT_ID, with the same datatype, precision and scale as the target key field.
  • Set the Lookup policy on multiple match property to Use Last Value.
  • Make the condition to be:

    SEQ_ID >= INPUT_ID

Sequence Generator Transformation

Add a Sequence Generator Transformation with the following settings:

  • Start Value = 0
  • Reset = True
  • All other properties as default.

Expression Transformation

Create an Expression Transformation with the following ports:

  • An input port, NEXTVAL, that is connected to the Sequence Generator Transformation.
  • A variable port, SEQ_VAR, with the following expression:

    IIF(SEQ_VAR= 0, IIF(ISNULL(:LKP.LKPTRANS(1)), 0, :LKP.LKPTRANS(1)), SEQ_VAR)

  • Create an output port with the following expression:

    SEQ_VAR + NEXTVAL

Link this output port to the primary key in the target.

More Information
Reference
Applies To
Product: PowerCenter
Problem Type:
User Type: Administrator
Project Phase:
Product Version: PowerCenter
Database:
Operating System:
Other Software:
Attachments
Last Modified Date:5/19/2010 3:15 PMID:10734
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)