RE: SQL how to update field with seq # starting with next # -- MIDRANGE-L

Hi,

But there can be a stopper: A sequence is not reset when a transaction is rolled back.

Regards,
-Arco

Tom E Stieger <testieger@xxxxxxxxxxxx> wrote:

WOW! That is so much easier than what I posted. Thanks for the info!

-Tom

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Thursday, July 05, 2012 10:06 PM
To: 'Midrange Systems Technical Discussion'
Subject: AW: SQL how to update field with seq # starting with next #

You also can use a Sequence object:
CREATE SEQUENCE MySchema/MySequ01
AS INTEGER
START WITH ???
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2147483647
NO CYCLE CACHE 20 ORDER ;

??? = Maximum Value in the table + 1

update MyTable
set MySeq = Next value for mysequ01
Where MySeq = 0;

Drop Sequence MySchema/MySequ01;;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Tom E Stieger
Gesendet: Friday, 06.7 2012 01:08
An: Midrange Systems Technical Discussion
Betreff: RE: SQL how to update field with seq # starting with next #

Try this:

create table teslib.table (seq dec(4,0), cust char(4), place char(15));

insert into teslib.table
values
(0000, '0101', 'walmart'),
(0000, '0102', 'target'),
(1234, '0104', 'McDonalds'),
(1235, '0105', 'Burger King');

Update teslib.table as A1 set A1.seq =
(select seqno from
(select case seq
when 0
then row_number() over() + (select max(seq) from
teslib.table)
else seq end as seqno,
cust, place from teslib.table) A2
where A2.cust = A1.cust);

Worked on v7.1 for me. I think there may be some issues if you want to fill in gaps in the sequence numbering, but this should help.

-Tom Stieger
IT Manager
California Fine Wire

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Thursday, July 05, 2012 3:07 PM
To: Midrange Systems Technical Discussion
Subject: SQL how to update field with seq # starting with next #

I have file as follows:

Seq# CUST# name
------ -------- -------------
0000 0101 walmart
0000 0102 target

1234 0104 McDonalds
1235 0105 Burger King

I would like to run an SQL stmt to change all the seq zero fields to the next higher #, so when finished, file would look like:

Seq# CUST# name
------ -------- -------------
1236 0101 walmart <-------------------- seq#
updated to next higher number
1237 0102 target <-------------------- seq#
updated to next higher number

1234 0104 McDonalds
1235 0105 Burger King

Can SQL do this easily?

Thanks!

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--
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.

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