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