RE: SQL Insert with an incrementing line number

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