Re: Incrementing a count within a file by using SQL

If you're at v5r4...

Row_number() is your friend.

select rcdtyp, partnbr, row_number() over (partition by partnbr order
by partnbr, rcdtyp)
from myTable;

Note, trying to update the existing records is going to be harder than
simply doing a select to build a new version of the table that
includes the line number then replacing the old with the new.
Basically, you have to build the temp table no matter what, it's just
a question of if it's a physical table or a Common table expression.

HTH,
Charles

On Thu, Jul 16, 2009 at 1:51 PM, Alan Shore<AlanShore@xxxxxxxx> wrote:

Afternoon all
I have a quandary that I'm hoping someone knows an answer to

I have a file
The file consists of 3 fields
Record type
Part number
Line number

For example, the data in this file is
Record type Part number       Line number
H           0000000001  00000
S           0000000001  00000
D           0000000001  00000
D           0000000001  00000
A           0000000001  00000
C           0000000001  00000
M           0000000001  00000
H           0000000002  00000
S           0000000002  00000
D           0000000002  00000
H           0000000003  00000
S           0000000003  00000
D           0000000003  00000
D           0000000003  00000
D           0000000003  00000
D           0000000003  00000

Using SQL, how can I increment the line number in the file so that the
first record of each part number is 00001, but the subsequent records (for
the same part number) is incremented by 1 for each record
so that the end result is
Record type Part number       Line number
H           0000000001  00001
S           0000000001  00002
D           0000000001  00003
D           0000000001  00004
A           0000000001  00005
C           0000000001  00006
M           0000000001  00007
H           0000000002  00001
S           0000000002  00002
D           0000000002  00003
H           0000000003  00001
S           0000000003  00002
D           0000000003  00003
D           0000000003  00004
D           0000000003  00005
D           0000000003  00006
etc



Thanks in anticipation


Alan Shore
Programmer/Analyst, Distribution
E:AShore@xxxxxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.