Re: Incrementing a count within a file by using SQL

[CRPence <CRPbottle@xxxxxxxxx>]

  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