Here is the input table:
MPID MGRID EMPNAME SALARY
----------- ----------- ------------------------- ---------
14 9 Didi 1500.00
1 - David 10000.00
2 1 Eitan 7000.00
3 1 Ina 7500.00
4 2 Seraph 5000.00
5 2 Jiru 5500.00
6 2 Steve 4500.00
7 3 Aaron 5000.00
8 5 Lilach 3500.00
9 7 Rita 3000.00
10 5 Sean 3000.00
11 7 Gabriel 3000.00
12 9 Emilia 2000.00
13 9 Michael 2000.00
14 record(s) selected.
output table:
-- Output
empid lvl lft rgt
——————— ———————
1 0 1 28
2 1 2 13
5 2 3 8
8 3 4 5
10 3 6 7
4 2 9 10
6 2 11 12
3 1 14 27
7 2 15 26
11 3 16 17
9 3 18 25
14 4 19 20
12 4 21 22
13 4 23 24
This NON DB2 CTE IS converting adjancey set model to a nested set model
with a left and right.
---------------------------------------------------------------------
-- CTE Code That Creates Nested Sets Relation****ps
---------------------------------------------------------------------
DECLARE @[EMAIL PROTECTED]
AS INT;
SET @[EMAIL PROTECTED]
= 1;
-- CTE with two numbers: 1 and 2
WITH TwoNumsCTE
AS
(
SELECT 1 AS n UNION ALL SELECT 2
),
-- CTE with two binary sort paths for each node:
-- One smaller than descendants sort paths
-- One greater than descendants sort paths
SortPathCTE
AS
(
SELECT empid, 0 AS lvl, n,
CAST(n AS VARBINARY(MAX)) AS sortpath
FROM dbo.Employees CROSS JOIN TwoNumsCTE
WHERE empid = @[EMAIL PROTECTED]
ALL
SELECT C.empid, P.lvl + 1, TN.n,
P.sortpath + CAST(
ROW_NUMBER() OVER(PARTITION BY C.mgrid
-- *** determines order of siblings ***
ORDER BY C.empname, C.empid, TN.n)
AS BINARY(4))
FROM SortPathCTE AS P
JOIN dbo.Employees AS C
ON P.n = 1
AND C.mgrid = P.empid
CROSS JOIN TwoNumsCTE AS TN
),
-- CTE with Row Numbers Representing sortpath Order
SortCTE
AS
(
SELECT empid, lvl,
ROW_NUMBER() OVER(ORDER BY sortpath) AS sortval
FROM SortPathCTE
),
-- CTE with Left and Right Values Representing
-- Nested Sets Relation****ps
NestedSetsCTE
AS
(
SELECT empid, lvl, MIN(sortval) AS lft, MAX(sortval) AS rgt
FROM SortCTE
GROUP BY empid, lvl
)
SELECT * FROM NestedSetsCTE ORDER BY lft;
I am trying to convert it to DB2:
WITH T0(ROOT) AS
(SELECT 1 FROM SYSIBM.SYSDUMMY1),
TwoNumsCTE(N) AS
(SELECT 1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 2 FROM SYSIBM.SYSDUMMY1
ORDER BY 1),
SortPathCTE(empid,lvl,n,sortpath) AS
(SELECT empid, 0, n,INTEGER(CAST(CHAR(n) AS varchar(300))) AS
sortpath
FROM dbo_Employees,T0
CROSS JOIN TwoNumsCTE
WHERE empid = ROOT
UNION ALL
SELECT C.empid, P.lvl + 1, TN.n,
P.sortpath + ROW_NUMBER() OVER(PARTITION BY C.mgrid
ORDER BY C.empname, C.empid, TN.n)
FROM SortPathCTE AS P
JOIN dbo_Employees AS C
ON P.n = 1
AND C.mgrid = P.empid
CROSS JOIN TwoNumsCTE AS TN)
SELECT * FROM SortPathCTE;
At this point i got an error:
sqlcode: -345
The fullselect of the recursive common table expressionmust be the UNION
of
two or more
fullselects and cannot include column functions, GROUP BY clause, HAVING
clause, ORDER BY clause.
Please help with conversion.
Thank's in advance. Leny G.
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200805/1
|
