MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » March 2014

Re: is there a proper method to get next number in sequence?



fixed

CREATE TABLE ROB/MIDRANGEL (
MLKEY INT NOT NULL GENERATED ALWAYS AS IDENTITY,
DESCRIPTION CHAR ( 50) NOT NULL,
CONSTRAINT MLKEY PRIMARY KEY (MLKEY),
CHECK (description<>' ' ))

See the identity column above?

INSERT INTO ROB/MIDRANGEL (DESCRIPTION) VALUES('test')

SELECT * FROM rob/midrangel
MLKEY DESCRIPTION
1 test

Need to know the key of the row just added? Perhaps you just added a row
to a order header table and you need that order number for adding rows to
the order line table.
VALUES IDENTITY_VAL_LOCAL()

Or to do the same thing with one shot:
SELECT mlkey
from final table (INSERT INTO ROB/MIDRANGEL
(DESCRIPTION)
VALUES('ROW2'))
MLKEY
2

And to really give you something to chew over...

CREATE TABLE ROB/MIDRANGE2 (
M2KEY INT NOT NULL GENERATED ALWAYS AS IDENTITY,
DESCRIPTION CHAR ( 50) NOT NULL,
CHANGE_TS TIMESTAMP
FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
NOT NULL
IMPLICITLY HIDDEN,
CHANGE_USER CHAR (18)
WITH DEFAULT USER
NOT NULL
IMPLICITLY HIDDEN,
CONSTRAINT M2KEY PRIMARY KEY (M2KEY),
CHECK (description<>' ' ))

SELECT M2KEY, CHANGE_TS, CHANGE_USER
FROM FINAL TABLE (
INSERT INTO ROB/MIDRANGE2
(DESCRIPTION) VALUES('test'))

M2KEY CHANGE_TS CHANGE_USER
1 2014-03-14-09.33.58.166241 ROB

SELECT * FROM ROB/MIDRANGE2
M2KEY DESCRIPTION
1 test

Rob Berendt





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact