Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Beginner
Mar 20, 2001

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 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

DevX Staff
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap