DB2 UDB V7 (Sequence Number)
This tip provided by Experts Exchange.
How can I add a sequence number to a target table based onone of its column’s values being the same?
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
When (Select Max(Line_Nb) + 1
WhereSource.Invc_Nb = Target.Invc_Nb) is
Else (Select Max(Line_Nb) + 1
WhereSource.Invc_Nb = Target.Invc_Nb)
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.
That is SO COOL! Thanks for the “OLAP Functions in DB2Help” suggestion as well.