Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2008
    Posts
    1

    Unanswered: Help with recursion

    I have a table

    CREATE TABLE [dbo].[chart_hiera2](
    [AccessID] [int] NULL,
    [ChildID] [int] NULL,
    [Child] [varchar](100) NULL,
    [ParentID] [int] NULL,
    [CGID] [int] NULL,
    [Depth] [smallint] NULL,
    [Lineage] [varchar](255) NULL,
    [node] [bit] NULL,
    [PercentOwnership] [varchar](10) NULL,
    [Notes] [varchar](80) NULL
    ) ON [PRIMARY]

    I am trying to build the value off hierarchy that will be later inserted in the linage and Depth column. I am trying to do so using recursion . the rulles for recording the linages as as follows Lineage = parent.Lineage + Ltrim(Str(ParentID,6,0)) + '/'

    here is my code below

    with BuildHierarchy as (
    SELECT AN.AccessID,AN.ChildID,AN.Child,AN.ParentID,AN.CGI D, 1 as Depth,AN.Lineage,AN.node,AN.PercentOwnership, AN.Notes
    FROM chart_hiera2 as AN WHERE AN.Depth Is Null and AN.AccessID = @accID
    union all
    SELECT AN.AccessID,AN.ChildID,AN.Child,AN.ParentID,AN.CGI D,Cast(AN.Depth as smallint) +1 ,Cast(BH.Lineage+ Ltrim(Str(AN.ParentID,6,0)) + '/' as varchar(255)),AN.node,AN.PercentOwnership, AN.Notes
    FROM chart_hiera2 as AN inner join BuildHierarchy BH on AN.ParentID=BH.ChildID
    WHERE AN.Depth>=0 AND AN.Lineage Is Not Null AND AN.Depth Is Null and AN.AccessID = @accID)--@accID --and T.AccessID = @accID)
    select * from BuildHierarchy

    but it does not increment Depth or builds Lineage .What am I doing wrong?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think you don't want to filter
    Code:
    AN.AccessID = @accID
    in the second part of your query. Just the first part, which serves to create the seed values. The second part should only be constrained by the seed values in the first.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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