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: Generate alphanumeric serial numbers using a PowerCenter mapping
Problem Description

The Sequence Generator only supports numeric values.
Is there a way to generate sequential values with character values that change whenever the sequence number reaches 999?

Example

Sequence numbers: 1001-1999
Desired values: A001-A999
Sequence numbers: 2000-2999
Desired values: B001-B999
etc.

Cause
Solution

Add the following to the mapping:

  1. Add a Sequence Generator transformation with the following properties:

    • Start value = 1000
    • Increment by = 1
    • End value = 9999
    • Current value = 1000

  2. Link the Nextval integer generated by the Sequence Generator to an Expression transformation.
    This number will start at 1000 to avoid getting zero as a return value.

  3. Add another output or variable port which will store the alphanumeric serial number.

  4. In the port created in step 3:

    1. Divide the sequence number by 1000
    2. Make the integer generated by the Sequence Generator into a string using TO_CHAR
    3. Using Decode output an alpha value according to the first digit of the number-string, i.e. 1 becomes A, 2 becomes B and so on
    4. Concatenate this output with the last three digits of the number (001-999). Use SUBSTR to get the values.

    Complete Formula:

    DECODE(TO_CHAR(TO_INTEGER(NEXTVAL /1000)),
    '1','A','2','B','3','C','4','D',5,'E') ||
    SUBSTR(TO_CHAR(NEXTVAL),-3,3)

More Information
Applies To
Product: PowerCenter
Problem Type:
User Type: Administrator
Project Phase:
Product Version: PowerCenter
Database:
Operating System:
Other Software:

Reference
Attachments
Last Modified Date:8/13/2010 3:24 PMID:10919
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)