Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2006
    Posts
    17

    Unanswered: Common Table Expressions (CTE) Question

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Look in Graeme Birchall's DB2 Cookbook under recursion. This should help you.

    http://mysite.verizon.net/Graeme_Birchall/id1.html

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •