Common Table Expressions (Cte) ON db2

[raysefo]

Hi,

ID NAME PARENTID
1 ABC
2 Sales 1
3 Avdertisement 2
4 Accounting 1
5 DEF
...

Select name from [mytable] where ID = 1

if ID = 1 than NAME is ABC
if ID = 2 than NAME is ABC/Sales
if ID = 3 than NAME is ABC/Sales/Advertisement
if ID = 4 than NAME is ABC/Accounting
and
if ID = 5 than NAME is DEF

How can i do a generic SELECT statement to do the sample above?
But above is just a sample, there may be 5 sub departments, may be 100.

I got the code for SQL 2005 but i need DB2 v 8.

Try this (SQL 2005 only):


Declare @ID int, @Dept varchar(max)
Set @ID = 3;

With DeptHier(ID, ParentID)
as
(
Select ID, ParentID
From Dept
Where ID = @ID

union all

Select Dept.ID, Dept.ParentID
From Dept JOIN DeptHier
On Dept.ID = DeptHier.ParentID
)
Select @dept = Coalesce(@dept + '/', '') + d.Name
from Dept d Join DeptHier dh
on d.ID = dh.ID
order by dh.ParentID

select @dept as Dept