RE: Incrementing a count within a file by using SQL

[Alan Shore <AlanShore@xxxxxxxx>]

I want to thank everyone who replied to what I thought was my quandary
Now that I've seen what the user is looking at (he shouldn't be), the line
number SHOULD in fact be 00000 at this point in the file that he is looking
at

NEVER MIND



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


                                                                       
             "DeLong, Eric"                                            
             <EDeLong@Sallybea                                         
             uty.com>                                                   To
             Sent by:                  "Midrange Systems Technical     
             midrange-l-bounce         Discussion"                     
             s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>       
                                                                        cc
                                                                       
             07/16/2009 02:59                                      Subject
             PM                        RE: Incrementing a count within a
                                       file by using SQL               
                                                                       
             Please respond to                                         
             Midrange Systems                                          
                 Technical                                             
                Discussion                                             
             <midrange-l@midra                                         
                 nge.com>                                              
                                                                       
                                                                       




Oops, I didn't read closely...  the subquery should only compare on part
number, not record type AND part number...

-Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Thursday, July 16, 2009 12:52 PM
To: Midrange Systems Technical Discussion
Subject: Incrementing a count within a file by using SQL

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.



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