Is there a way to use something like mssql's "case"? I have a table
that holds hierarchy in nested sets and I tried to make procedure,
which will add new node in hierarchy. Table looks something like this:
create table hierarchy
(
id integer DEFAULT SERIAL(1),
node varchar(16),
left integer,
right integer
)
I tried with several approaches but every time I call new procedure it
falls in some kind of endless loop.
Mssql procedure looks like this:
CREATE PROCEDURE SP_INSERT_HIER_NODE
@PARENT_ID int,
@NAME nvarchar(16),
AS
DECLARE
@PARENT_RIGHT AS int
SELECT
@PARENT_RIGHT = [RIGHT]
FROM
HIERARCHY
WHERE
ID = @PARENT_ID;
UPDATE
HIER
SET
[LEFT] = CASE
WHEN [LEFT] > @PARENT_RIGHT
THEN [LEFT] + 2
ELSE [LEFT]
END,
[RIGHT] = CASE
WHEN [RIGHT] >= @PARENT_RIGHT
THEN [RIGHT] + 2
ELSE [RIGHT]
END
WHERE
[RIGHT] >= @PARENT_RIGHT;
INSERT INTO
HIERARCHY (NODE, [LEFT], [RIGHT])
VALUES
(@NAME, @PARENT_RIGHT, @PARENT_RIGHT + 1);
GO
I tried with:
CREATE DBPROC SP_INSERT_HIER_NODE (
IN NAME VARCHAR(16),
IN PARENT INTEGER
)
AS
VAR LEFT_ INTEGER; RIGHT_ INTEGER; PARENT_LEFT INTEGER; PARENT_RIGHT
INTEGER; TEMP_ID INTEGER; ROWCOUNT INTEGER;
SELECT
"LEFT", "RIGHT"
FROM
RBAC.HIERARCHY
WHERE
HIERARCHY.ID = :PARENT;
FETCH INTO :PARENT_LEFT, :PARENT_RIGHT;
SELECT
COUNT(*)
FROM
RBAC.HIERARCHY
WHERE
HIERARCHY."RIGHT" >= :PARENT_RIGHT;
FETCH INTO :ROWCOUNT;
WHILE ROWCOUNT > 0 DO BEGIN
SELECT
ID,"LEFT","RIGHT"
FROM
RBAC.T_ROLE
WHERE
HIERARCHY.RIGHT >= :PARENT_RIGHT;
FETCH INTO :TEMP_ID, :LEFT_, :RIGHT_;
IF
LEFT_ > PARENT_RIGHT
THEN
SET LEFT_ = LEFT_ + 2;
IF
RIGHT_ >= PARENT_RIGHT
THEN
SET RIGHT_ = RIGHT_ + 2;
UPDATE
RBAC. HIERARCHY
SET
"LEFT" = :LEFT_
WHERE
HIERARCHY.ID = :TEMP_ID;
UPDATE
RBAC. HIERARCHY
SET
"RIGHT" = :RIGHT_
WHERE
HIERARCHY.ID = :TEMP_ID;
SELECT
COUNT(*)
FROM
RBAC. HIERARCHY
WHERE
HIERARCHY."RIGHT" >= :PARENT_RIGHT;
FETCH INTO :ROWCOUNT;
END;
What is wrong there?
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
