By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,677 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

Common Table Expressions (Cte) ON db2

P: 4
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
Feb 18 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.