Rik,

I fear there is no beautifull solution except to drop the sequence
number and try to do the ordering by other means, e. g. part code or
part name. The main problem is that SQL does set oriented processing
and as such is not good to handle one row at a time. All the table
inserts, updates and deletes would have to go via a special stored
procedure in order to avoid that the sequence numbers do not get out
of sync (Some good examples here: http://unixadmintalk.com/f27/sequent...pdate-267509/).
If you update the table directly you easily destroy the sequence
number and then suppose you have a query that relies on the fact you
always have a sequence number 1 and at some places it is not available
- then your results are destroyed and there is much fun in repairing
this.

brgds

Philipp Post

Rik,

I fear there is no beautifull solution except to drop the sequence
number and try to do the ordering by other means, e. g. part code or
part name. The main problem is that SQL does set oriented processing
and as such is not good to handle one row at a time. All the table
inserts, updates and deletes would have to go via a special stored
procedure in order to avoid that the sequence numbers do not get out
of sync (Some good examples here: http://unixadmintalk.com/f27/sequent...pdate-267509/).
If you update the table directly you easily destroy the sequence
number and then suppose you have a query that relies on the fact you
always have a sequence number 1 and at some places it is not available
- then your results are destroyed and there is much fun in repairing
this.

brgds

Philipp Post

Rik,

I fear there is no beautifull solution except to drop the sequence
number and try to do the ordering by other means, e. g. part code or
part name. The main problem is that SQL does set oriented processing
and as such is not good to handle one row at a time. All the table
inserts, updates and deletes would have to go via a special stored
procedure in order to avoid that the sequence numbers do not get out
of sync (Some good examples here: http://unixadmintalk.com/f27/sequent...pdate-267509/).
If you update the table directly you easily destroy the sequence
number and then suppose you have a query that relies on the fact you
always have a sequence number 1 and at some places it is not available
- then your results are destroyed and there is much fun in repairing
this.

brgds

Philipp Post

Let me try to come with better column names. Unfortunately, your
specs are weak and it is hard to guess what the key is since you did
not bother with DDL.

CREATE TABLE Products
(product_id INTEGER NOT NULL,
part_nbr CHAR(1) NOT NULL,
PRIMARY KEY (product_id, part_nbr),
-- PRIMARY KEY (product_id, assembly_seq)??
assembly_seq INTEGER NOT NULL);

If you have assertions, then you can do this:

CREATE ASSERTION AssemblySequenceCheck
AS NOT EXISTS
(SELECT product_id
FROM Products
GROUP BY product_id
HAVING COUNT(*) <> MAX(assembly_seq);

If your SQL is not up to full ANSI specs yet, try this

CREATE VIEW SequencedProducts (product_id, part_nbr, assembly_seq)
AS
SELECT product_id, part_nbr, assembly_seq
FROM Products AS P1
WHERE NOT EXISTS
(SELECT product_id
FROM Products AS P2
WHERE P1.product_id = P2.product_id
AND COUNT(*) <> MAX(assembly_seq)
WITH CHECK OPTION;

Let me try to come with better column names. Unfortunately, your
specs are weak and it is hard to guess what the key is since you did
not bother with DDL.

CREATE TABLE Products
(product_id INTEGER NOT NULL,
part_nbr CHAR(1) NOT NULL,
PRIMARY KEY (product_id, part_nbr),
-- PRIMARY KEY (product_id, assembly_seq)??
assembly_seq INTEGER NOT NULL);

If you have assertions, then you can do this:

CREATE ASSERTION AssemblySequenceCheck
AS NOT EXISTS
(SELECT product_id
FROM Products
GROUP BY product_id
HAVING COUNT(*) <> MAX(assembly_seq);

If your SQL is not up to full ANSI specs yet, try this

CREATE VIEW SequencedProducts (product_id, part_nbr, assembly_seq)
AS
SELECT product_id, part_nbr, assembly_seq
FROM Products AS P1
WHERE NOT EXISTS
(SELECT product_id
FROM Products AS P2
WHERE P1.product_id = P2.product_id
AND COUNT(*) <> MAX(assembly_seq)
WITH CHECK OPTION;

Let me try to come with better column names. Unfortunately, your
specs are weak and it is hard to guess what the key is since you did
not bother with DDL.

CREATE TABLE Products
(product_id INTEGER NOT NULL,
part_nbr CHAR(1) NOT NULL,
PRIMARY KEY (product_id, part_nbr),
-- PRIMARY KEY (product_id, assembly_seq)??
assembly_seq INTEGER NOT NULL);

If you have assertions, then you can do this:

CREATE ASSERTION AssemblySequenceCheck
AS NOT EXISTS
(SELECT product_id
FROM Products
GROUP BY product_id
HAVING COUNT(*) <> MAX(assembly_seq);

If your SQL is not up to full ANSI specs yet, try this

CREATE VIEW SequencedProducts (product_id, part_nbr, assembly_seq)
AS
SELECT product_id, part_nbr, assembly_seq
FROM Products AS P1
WHERE NOT EXISTS
(SELECT product_id
FROM Products AS P2
WHERE P1.product_id = P2.product_id
AND COUNT(*) <> MAX(assembly_seq)
WITH CHECK OPTION;