Re: SQL Insert I need a line#/seq#

Hi Jack,

You can alternatively use the ROW_NUMBER function in place of the 1 to
calculate the sequence#, without the need for creating a sequence object:

row_number() over( partition by [list of columns that reset sequence# to 1]
order by [optional list of columns to control
how sequence# is doled out within a partition]
) as sequence#

Replace [list of column(s) that reset sequence# to 1] with one or more
column names separated by commas. The partition defines each group of
related rows, and defines when the sequence# is reset to 1.

Optionally specify an order by clause within row_number(), specifying one
of more column names separated by commas, to control how the calculated
value is incremented within each partition group of rows.

So, let's say you were adding rows to an ORDER_DETAIL table, which uses a
SEQUENCE# for order detail lines, and you wanted:
1) The sequence# to reset for each order#, and
2) The sequence# to be assigned to the largest quantities first, followed
by smaller quantities, within each order.

That would look like this:

insert into ORDER_DETAIL
( ORDER#, SEQUENCE#, ITEM#, QUANTITY )

select ORDER#
,row_number() over( partition by ORDER# order by QUANTITY desc )
as SEQUENCE#
,ITEM#
,QUANTITY
from ORDER_BATCH

...that results in each order having its own set of sequence numbers
starting from 1, plus within each order, rows with a larger quantity value
will be assigned a smaller sequence# than rows with a smaller quantity.

Mike


date: Mon, 14 Aug 2017 10:53:40 -0400

from: Jack Tucky <jacktucky@xxxxxxxxx>
subject: SQL Insert I need a line#/seq#

I have this code that originally had a group by so that one line was always
inserted into WSKUSUM99. The "1" after the field job is the sequence#.


exec Sql
insert into wskusum99 (
job , 1 , next value for styhs2r, msc1, msc2,shp,sea,sty,clr,
fab,mod,msz,qt2,qt3)

select :#sdjb#, seq,
0,'a','PHYSICAL COUNT',ls.shp, ls.sea, ls.sty, ls.clr,
ls.fab, ls.mod, ls.msz,
ls.qty, ls.qty

How can I change the 1 to an incrementing field? The table column is
defined as seq (3,0)

I found this code to create a sequence. I But I would like to reset it to 1
every time. How do I do that? Also,how do I put "next value for styhs2r"
into the insert statement?

exec Sql
create sequence styhs2r
start with 1
increment by 1
no maxvalue
no cycle;

Thanks