RE: SQL Insert with an incrementing line number

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Are you on V5R4?  If so, then something like this ought to work:

INSERT INTO toFile                                            
(SELECT order#,                                               
        ROW_NUMBER () OVER(PARTITION BY order# ORDER BY order#)
 FROM fromFile)                                               

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com

-----Original Message-----
Subject: SQL Insert with an incrementing line number

Hey all,   can this be done with sql?

insert from one file into another -

"from" file has duplicates for order#, no line.
"to" file keyed by order# / line

I want to auto generate the line number in the "to" file, incrementing
by one for each group of orders, resetting on a change in order
numbers.

I looked at NEXT VALUE using sequence objects, but that doesn't seem
to allow the reset on order number breaks.

Thanks in advance,

Rick