Re: Incrementing a count within a file by using SQL

FWiW the given data is missing any obvious key, so what is shown is assumed to be non-relational data. Although invariably claims will be made that the SQL can be used to effect what is desired, that desired effect might come only by chance without a key; i.e. a sequential access path may be the chosen implementation for accessing the data, but relationally, there is no guarantee of the order the data is accessed. There needs to be a key for which the data has the collation currently presented, in order to be relational data. If the data is stored sequentially as presented, then a RRN() would suffice as the key; that was not stated as a given however, and REUSEDLT(*YES) is a possible issue. Another possible key [as inferred from what data is presented] is if the record type always has the collation "HSDACM", then a sort sequence table can be created and then used to effect that ordering; IIRC, prompt CRTTBL.

Regards, Chuck

Alan Shore wrote:

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