DB2 UDB V7 (Sequence Number)

This tip provided by Experts Exchange

DB2 UDB V7 (Sequence Number)

 

This tip provided by Experts Exchange.

 

Question:

How can I add a sequence number to a target table based onone of its column’s values being the same?

Example:

SourceTable:
Invoice_Nb
———-
1
1
1
2
2
3
4

Desired TargetTable:
Invoice_Nb      Line_Nb
———-      ——–
1              1
1              2
1              3
2              1
2              2
3              1
4              1

I have tried the following command, but get all Line_nb’s equal to 1. (I assumebecause the inserts
are not committed until the end.)

Insert Into TargetTable
Select
  Invc_Nb,
  Case
     When (Select Max(Line_Nb) + 1
           FromTartetTable
           WhereSource.Invc_Nb = Target.Invc_Nb) is
                                         nullthen 1
     Else (Select Max(Line_Nb) + 1
           FromTartetTable
           WhereSource.Invc_Nb = Target.Invc_Nb)
  End,
From SourceTable

 

Accepted Answer:

INSERT INTO TargetTable
SELECT Invc_Nb, ROW_NUMBER() OVER (PARTITION BY Invc_Nb ORDER BY Invc_Nb) ASLine_Nb FROM SourceTable;

For further information on this type of query check under the OLAP functionallyin the DB2 Help.

 

Comment:

That is SO COOL! Thanks for the “OLAP Functions in DB2Help” suggestion as well.

 

Written on 2/07/2001

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS