Hi,


I have a table TESTTREE representing a tree structure with the nested
sets model.

CREATE TABLE TESTTREE (
ID INTEGER NOT NULL,
LFT INTEGER NOT NULL,
RGT INTEGER NOT NULL,
NODENAME VARCHAR(20) NOT NULL);


For inserting a new node I specify its position in the tree by giving
the parent's ID and
its rank among other children of this parent.
My problem is to get the RGT value of the CHILDRANK - 1 child? How does the
SQL statement look like?

CREATE PROCEDURE INS_NODE (
IDPARENT INTEGER,
CHILDRANK INTEGER,
NODENAME VARCHAR(20))
AS
DECLARE VARIABLE NEW_LFT INTEGER;
DECLARE VARIABLE NEW_RGT INTEGER;
DECLARE VARIABLE PRNT_LFT INTEGER;
DECLARE VARIABLE PRNT_RGT INTEGER;
BEGIN
/*
Determine the right nested sets value. Every LFT and RGT equal or
higher must be
increased by 2. Then the actual insert can be done.
*/

-- Get LFT and RGT of the parent
SELECT LFT, RGT
FROM TESTTREE
WHERE ID = :IDPARENT
INTO :PRNT_LFT, :PRNT_RGT;

-- Determine NEW_RGT according to the CHILDRANK specified.
IF (PRNT_RGT - PRNT_LFT = 1) OR (CHILDRANK = 1) THEN
-- The parent has no children yet, so CHILDRANK doesn't matter or
the leftmost position is specified.
NEW_LFT = PRNT_LFT + 1;
ELSE IF ((CHILDRANK IS NULL) OR (CHILDRANK = 0)) THEN
-- Append the new childnode at the right
NEW_LFT = PRNT_RGT;
ELSE
BEGIN
-- Get the RGT value of the child left of the new node.
?????
????????
END
NEW_RGT = NEW_LFT + 1;

-- Update LFT and RGT values of all nodes after the new one
UPDATE TESTTREE
SET LFT = CASE WHEN LFT >= NEW_LFT THEN LFT + 2 ELSE LFT,
RGT = CASE WHEN RGT >= NEW_RGT THEN RGT + 2 ELSE RGT

-- Do the actual insert of the new node
INSERT INTO TESTTREE
(LFT, RGT, NODENAME)
VALUES
(:NEW_LFT, :NEW_RGT, :NODENAME);
END