Re: Extracting Parent Model from Nested Sets Model

From: Flavio Botelho <nuncanada_at_ig.com.br>
Date: 23 Jan 2003 10:15:32 -0800
Message-ID: <de0df942.0301231015.9f2e9ee_at_posting.google.com>

Guessing from your query, i am extrapolating that maybe this way of updating next_child might work if "SET _at_next_child int" returns the int: '
  SELECT
(SET _at_next_child

      CASE 
        WHEN lft >= _at_next_child 
        THEN rgt+1 
        ELSE _at_next_child 
      END) AS next_child, '

Nuncanada

Snippet for test:

 CREATE TABLE Frammis
 (part CHAR(2) NOT NULL
  lft INTEGER NOT NULL,
  rgt INTEGER NOT NULL);  

 INSERT INTO Frammis VALUES ('A', 1, 30);
 INSERT INTO Frammis VALUES ('B', 2, 5);
 INSERT INTO Frammis VALUES ('C', 6, 21);
 INSERT INTO Frammis VALUES ('D', 22, 29);
 INSERT INTO Frammis VALUES ('E', 3, 4);
 INSERT INTO Frammis VALUES ('F', 7, 16);
 INSERT INTO Frammis VALUES ('F1', 17, 18);
 INSERT INTO Frammis VALUES ('G', 19, 20);
 INSERT INTO Frammis VALUES ('H', 23, 28);
 INSERT INTO Frammis VALUES ('I', 8, 9);
 INSERT INTO Frammis VALUES ('J', 10, 15);
 INSERT INTO Frammis VALUES ('K', 24, 25);
 INSERT INTO Frammis VALUES ('L', 26, 27);
 INSERT INTO Frammis VALUES ('M', 11, 12);
 INSERT INTO Frammis VALUES ('N', 13, 14);

BEGIN

  DECLARE _at_next_brother INTEGER;
  DECLARE _at_parent_left INTEGER;
  DECLARE _at_parent_right INTEGER;

  SET _at_parent_left = 7;
  SET _at_parent_right = 16;

  SET _at_next_child = @parent_left+1;  

  SELECT
(SET _at_next_child

      CASE 
        WHEN lft >= _at_next_child 
        THEN rgt+1 
        ELSE _at_next_child 
      END) AS next_child, 
      part AS parent

    FROM Frammis
    WHERE lft > _at_parent_left AND rgt < @parent_right     HAVING next_child = rgt+1
    ORDER BY lft;
END; Received on Thu Jan 23 2003 - 19:15:32 CET