Rolf


Please post the GroupsTree procedure.

Roy Lambert [Team Elevate]

Roy,

I have solved the problem like this:

SCRIPT
BEGIN
  DECLARE ReturnCursor SENSITIVE CURSOR WITH RETURN FOR TreeStmt;

  CALL GroupsTree(554, 1);
  PREPARE TreeStmt FROM
    'SELECT * FROM TmpGroupsTree ORDER BY SortNo, SortID';

  OPEN ReturnCursor;
END

I open now the created temp table after the call. For testing this may be a
workaround.

But anyway, here are the two Procedures:
---------------------------
CREATE PROCEDURE "GroupsTree" (IN "ID" INTEGER, IN "Language" SMALLINT)
BEGIN
  DECLARE GrpCursor CURSOR FOR GrpStmt;
  DECLARE GrpID INTEGER;
  DECLARE GrpParentID INTEGER;
  DECLARE GrpTreeLevel INTEGER;
  DECLARE GrpPartCount INTEGER;
  DECLARE GrpDescription VARCHAR DEFAULT '';
  DECLARE GrpSortID VARCHAR DEFAULT '';
  DECLARE GrpSortNo INTEGER DEFAULT 0;
  DECLARE InsertStmt STATEMENT;
  DECLARE ReturnCursor SENSITIVE CURSOR WITH RETURN FOR TreeStmt;

  BEGIN
  EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE "TmpGroupsTree"
                    (
                    "ID" INTEGER,
                    "ParentID" INTEGER,
                    "TreeLevel" INTEGER,
                    "SortNo" INTEGER,
                    "PartCount" INTEGER,
                    "Description" VARCHAR(50),
                    "SortID" VARCHAR(10),
                    PRIMARY KEY ("ID")
                    )';

  EXECUTE IMMEDIATE 'CREATE INDEX "idxSortID" ON "TmpGroupsTree" ("SortNo",
"SortID")';

  EXCEPTION
     IF ERRORCODE()=400 THEN
       EXECUTE IMMEDIATE 'DELETE FROM TmpGroupsTree';
     ELSE
        RAISE ERROR CODE 10000 MESSAGE 'Unexpected error - ' + ERRORMSG() +
' (Error Code ' + CAST(ERRORCODE() AS VARCHAR) + ')';
     END IF;
  END;


  PREPARE InsertStmt FROM
     'INSERT INTO TmpGroupsTree
        (ID, ParentID, TreeLevel, SortNo, PartCount, Description, SortID)
        VALUES (?, ?, ?, ?, ?, ?, ?)';

  PREPARE GrpStmt FROM
    'SELECT g.ID, g.ParentID, g.TreeLevel, g.PartCount, gt.Description,
gt.SortID FROM Groups g
       JOIN GroupsText gt ON (gt.ID, gt.Language) = (g.ID, ?)
       WHERE (g.ParentID = ? or g.ID = ?) ORDER BY SortID';

  OPEN GrpCursor USING Language, ID, ID;

  FETCH FIRST FROM GrpCursor ('ID', 'ParentID', 'TreeLevel', 'PartCount',
'Description', 'SortID')
     INTO GrpID, GrpParentID, GrpTreeLevel, GrpPartCount, GrpDescription,
GrpSortID;

  SET GrpSortNo = 1000;
  WHILE NOT EOF(GrpCursor) DO
     IF GrpID = ID THEN
        CALL GroupsTree_(GrpID, GrpParentID, Language, GrpSortNo - 1,
GrpSortNo + 1);
        SET GrpSortNo = 1000;
     ELSE
        EXECUTE InsertStmt USING GrpID, GrpParentID, GrpTreeLevel,
GrpSortNo, GrpPartCount, GrpDescription, GrpSortID;
     END IF;

     FETCH NEXT FROM GrpCursor ('ID', 'ParentID', 'TreeLevel', 'PartCount',
'Description', 'SortID')
        INTO GrpID, GrpParentID, GrpTreeLevel, GrpPartCount,
GrpDescription, GrpSortID;
  END WHILE;

  CLOSE GrpCursor;

  PREPARE TreeStmt FROM 'SELECT * FROM TmpGroupsTree ORDER BY SortNo,
SortID';
  OPEN ReturnCursor;


END
VERSION 1.00
--------------------------------------------------------

CREATE PROCEDURE "GroupsTree_" (IN "ID" INTEGER, IN "ParentID" INTEGER, IN
"Language" SMALLINT, IN "SortNoTop" INTEGER, IN "SortNoBottom" INTEGER)
BEGIN
  DECLARE GrpCursor CURSOR FOR GrpStmt;
  DECLARE GrpID INTEGER;
  DECLARE GrpParentID INTEGER;
  DECLARE GrpTreeLevel INTEGER;
  DECLARE GrpPartCount INTEGER;
  DECLARE GrpDescription VARCHAR DEFAULT '';
  DECLARE GrpSortID VARCHAR DEFAULT '';
  DECLARE InsertStmt STATEMENT;
  DECLARE TmpSortNo INTEGER DEFAULT 0;


  PREPARE InsertStmt FROM
    'INSERT INTO TmpGroupsTree
       (ID, ParentID, TreeLevel, SortNo, PartCount, Description, SortID)
       VALUES (?, ?, ?, ?, ?, ?, ?)';

  PREPARE GrpStmt FROM
    'SELECT g.ID, g.ParentID, g.TreeLevel, g.PartCount, gt.Description,
gt.SortID FROM Groups g
       JOIN GroupsText gt ON (gt.ID, gt.Language) = (g.ID, ?)
       WHERE (g.ParentID = ? or ID = ?) ORDER BY SortID';

  OPEN GrpCursor USING Language, ParentID, ParentID;
--SET LOG MESSAGE TO 'ID=' + Cast(ID AS VARCHAR) + ' / ParentID=' +
Cast(ParentID AS VARCHAR);

  FETCH FIRST FROM GrpCursor ('ID', 'ParentID', 'TreeLevel', 'PartCount',
'Description', 'SortID')
     INTO GrpID, GrpParentID, GrpTreeLevel, GrpPartCount, GrpDescription,
GrpSortID;

  SET TmpSortNo = SortNoTop;
  WHILE NOT EOF(GrpCursor) DO
     IF (GrpID = ParentID) THEN
       CALL GroupsTree_(GrpID, GrpParentID, Language, SortNoTop - 1,
SortNoBottom + 1);
     ELSE
        EXECUTE InsertStmt USING GrpID, GrpParentID, GrpTreeLevel,
TmpSortNo, GrpPartCount, GrpDescription, GrpSortID;
        IF (ID = GrpID) THEN
           SET TmpSortNo = SortNoBottom;
        END IF;
     END IF;

     FETCH NEXT FROM GrpCursor ('ID', 'ParentID', 'TreeLevel', 'PartCount',
'Description', 'SortID')
       INTO GrpID, GrpParentID, GrpTreeLevel, GrpPartCount, GrpDescription,
GrpSortID;

  END WHILE;

  CLOSE GrpCursor;
END
VERSION 1.00
---------------------------------------------------------

FYI: This Procedure does create a Tree from a table with ID/ParentID
structure to build a product catalog tree to the selected category (passed
paramter).

Regards
Rolf

Rolf

>I open now the created temp table after the call. For testing this may be a
>workaround.

I think this may be more permanent than workaround.

>FYI: This Procedure does create a Tree from a table with ID/ParentID
>structure to build a product catalog tree to the selected category (passed
>paramter).

I'll take your word for that

They're far more complex than I was anticipating and I think you've found the best solution.

Roy Lambert [Team Elevate]