Thanks. You have a modern version of Sybase.
Try the following query.
--
with emp (emp_id, emp_name, emp_desg, emp_mgr) as (
select 10, 'Adam', 'CEO', null union all
select 20, 'Brian', 'President', 10 union all
select 30, 'Chuck', 'VP', 10 union all
select 40, 'Dan', 'Architect', 20 union all
select 50, 'Eric', 'SSE', 20 union all
select 60, 'Fred', 'Team Lead', 30 union all
select 70, 'Greg', 'SE', 60
),
hier_tree (emp_id, emp_name, emp_desg, emp_mgr, level, path) as (
-- Seed query
select emp_id, emp_name, emp_desg, emp_mgr, 1 as level, cast('/' + emp_name as varchar) as path
from emp
where emp_mgr is null
union all
-- Recursive query
select e.emp_id, e.emp_name, e.emp_desg, e.emp_mgr, h.level + 1, cast(h.path + '/' + e.emp_name as varchar)
from hier_tree h, emp e
where h.emp_id = e.emp_mgr
)
select *
from hier_tree
I do not have Sybase db to test it; I tested it in SQL Server 2008.
Since Sybase and SQL Server share a common code base, it should work in Sybase, maybe with a few minor tweaks.
Here's my execution:
3>
4> select @@version
5> go
------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
Aug 19 2014 12:21:34
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
(1 rows affected)
1>
2> --
3> with emp (emp_id, emp_name, emp_desg, emp_mgr) as (
4> select 10, 'Adam', 'CEO', null union all
5> select 20, 'Brian', 'President', 10 union all
6> select 30, 'Chuck', 'VP', 10 union all
7> select 40, 'Dan', 'Architect', 20 union all
8> select 50, 'Eric', 'SSE', 20 union all
9> select 60, 'Fred', 'Team Lead', 30 union all
10> select 70, 'Greg', 'SE', 60
11> ),
12> hier_tree (emp_id, emp_name, emp_desg, emp_mgr, level, path) as (
13> -- Seed query
14> select emp_id, emp_name, emp_desg, emp_mgr, 1 as level, cast('/' + emp_name as varchar) as path
15> from emp
16> where emp_mgr is null
17> union all
18> -- Recursive query
19> select e.emp_id, e.emp_name, e.emp_desg, e.emp_mgr, h.level + 1, cast(h.path + '/' + e.emp_name as varchar)
20> from hier_tree h, emp e
21> where h.emp_id = e.emp_mgr
22> )
23> select *
24> from hier_tree
25> go
emp_id emp_name emp_desg emp_mgr level path
----------- -------- --------- ----------- ----------- ------------------------------
10 Adam CEO NULL 1 /Adam
20 Brian President 10 2 /Adam/Brian
30 Chuck VP 10 2 /Adam/Chuck
60 Fred Team Lead 30 3 /Adam/Chuck/Fred
70 Greg SE 60 4 /Adam/Chuck/Fred/Greg
40 Dan Architect 20 3 /Adam/Brian/Dan
50 Eric SSE 20 3 /Adam/Brian/Eric
(7 rows affected)
1>
2>
3>
A few notes follow:
(1) Sybase might require you to use the keyword "recursive" after the keyword "with". So "with emp..." becomes "with recursive emp...". It is optional as per ANSI SQL standard. Some databases like PostgreSQL require it; others like Oracle and DB2 don't.
(2) The "with emp" subquery was created by me to generate the data on-the-fly, without having to create a table. You already have the emp data in a table, so you may want to use that table. If you do that, then your query begins from "hier_tree". So: "with hier_tree..." or "with recursive hier_tree..." - whichever works.
(3) Concatenation operator "+" should work in Sybase.
(4) Not sure about the "cast()" function in Sybase. SQL Server throws an error message if I don't use it.