DB2 UDB V7 (Sequence Number)

This tip provided by Experts Exchange.

Question:

How can I add a sequence number to a target table based on one 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 assume because the inserts
are not committed until the end.)

Insert Into TargetTable
Select
  Invc_Nb,
  Case
     When (Select Max(Line_Nb) + 1
           From TartetTable
           Where Source.Invc_Nb = Target.Invc_Nb) is
                                         null then 1
     Else (Select Max(Line_Nb) + 1
           From TartetTable
           Where Source.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) AS Line_Nb FROM SourceTable;

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

Comment:

That is SO COOL! Thanks for the "OLAP Functions in DB2 Help" suggestion as well.

Written on 2/07/2001