LeGbvzvc

1SortPath =  CAST(
2                    CAST(anchor.EmployeeID AS BINARY(4)) 
3                AS VARBINARY(4000)) --Up to 1000 levels deep.
4	
5CAST(CAST(anchor.EmployeeID AS BINARY(4)) AS BINARY(4000)) AS sort_path ## Also with 8000
6	
7Truncated incorrect DOUBLE value: '1x00x00x00'
8	
9##===== Conditionally drop Temp tables to make reruns easy
10DROP TABLE IF EXISTS hierarchy;
11
12##===== Build the new table on-the-fly including some place holders
13CREATE TABLE hierarchy AS
14
15WITH RECURSIVE cteBuildPath AS 
16( ##=== This is the "anchor" part of the recursive CTE.
17     ## The only thing it does is load the Root Node.
18 SELECT anchor.tree_user_id, 
19        anchor.upline_id, 
20        1 AS hlevel,
21        CAST(anchor.tree_user_id AS BINARY(4)) AS sort_path ##Up to 1000 levels deep.
22  FROM example.tree AS anchor
23  WHERE upline_id IS NULL ##Only the Root Node has a NULL ManagerID
24  UNION ALL 
25 ##==== This is the "recursive" part of the CTE that adds 1 for each level
26     ## and concatenates each level of EmployeeID's to the SortPath column.  
27 SELECT recur.tree_user_id, 
28        recur.upline_id, 
29        cte.HLevel + 1,
30        cte.sort_path + CAST(recur.tree_user_id AS BINARY(4))
31   FROM example.tree AS recur
32  INNER JOIN cteBuildPath AS cte 
33          ON cte.tree_user_id = recur.upline_id
34) ##=== This final SELECT/INTO creates the Node # in the same order as a
35     ## push-stack would. It also creates the final table with some
36     ## "reserved" columns on the fly. We'll leave the SortPath column in
37     ## place because we're still going to need it later.
38     ## The ISNULLs make NOT NULL columns.
39 SELECT IFNULL(sorted.tree_user_id,0) AS tree_user_id,
40        sorted.upline_id,
41        IFNULL(sorted.hlevel,0) AS hlevel,
42      #  IFNULL(CAST(0 AS INT),0) AS left_bower, ##Place holder
43      #  IFNULL(CAST(0 AS INT),0) AS right_bower, ##Place holder
44      (@n := @n + 1) AS node_number,
45      #  IFNULL(CAST(0 AS INT),0) AS node_count, ##Place holder
46        IFNULL(sorted.sort_path,sorted.sort_path) AS sort_path
47   FROM cteBuildPath AS sorted
48   JOIN (SELECT @n := 0) n ON 1=1
49   #ORDER BY sorted.sort_path
50 #OPTION (MAXRECURSION 100) ##Change this IF necessary
51;