Need help with a recursive function - Setting a column value depending on column value of child rows

• Veda*

  Newbie

  Points: 9

  More actions

  June 17, 2016 at 1:41 pm

  #308540

  I need help with my SQL query. I have the following table structure:

  

  From this, I am getting a hierarchical XML using a recursive function and I have got this far:

  <AssignTrees>

  <AssignTree ID="1" ParentID="0" TreeDesc="SD1" IsSelected="0"> (this should be NULL)

  <ChildAssigns>

  <AssignTree ID="11" ParentID="1" TreeDesc="SD6" IsSelected="0"> (this should be 1)

  <ChildAssigns>

  <AssignTree ID="111" ParentID="11" TreeDesc="SD8" IsSelected="1">

  <AssignDetail Assign="Rule6" Desc="Desc6" />

  </AssignTree>

  <AssignTree ID="112" ParentID="11" TreeDesc="SD15" IsSelected="1">

  <AssignDetail Assign="Rule5" Desc="Desc5" />

  </AssignTree>

  </ChildAssigns>

  </AssignTree>

  <AssignTree ID="12" ParentID="1" TreeDesc="SD7" IsSelected="0"> (this should be 1)

  <ChildAssigns>

  <AssignTree ID="121" ParentID="12" TreeDesc="SD9" IsSelected="1">

  <AssignDetail Assign="Rule2" Desc="Desc2" />

  </AssignTree>

  <AssignTree ID="122" ParentID="12" TreeDesc="SD10" IsSelected="1">

  <AssignDetail Assign="Rule3" Desc="Desc3" />

  </AssignTree>

  </ChildAssigns>

  </AssignTree>

  <AssignTree ID="13" ParentID="1" TreeDesc="SD16" IsSelected="0">

  <ChildAssigns>

  <AssignTree ID="131" ParentID="13" TreeDesc="SD17" IsSelected="0">

  <AssignDetail Assign="Rule7" Desc="Desc7" />

  </AssignTree>

  <AssignTree ID="132" ParentID="13" TreeDesc="SD18" IsSelected="0">

  <AssignDetail Assign="Rule8" Desc="Desc8" />

  </AssignTree>

  </ChildAssigns>

  </AssignTree>

  </ChildAssigns>

  </AssignTree>

  <AssignTree ID="2" ParentID="0" TreeDesc="SD2" IsSelected="0"> (this should be NULL)

  <ChildAssigns>

  <AssignTree ID="21" ParentID="2" TreeDesc="SD11" IsSelected="0"> (this should be 1)

  <ChildAssigns>

  <AssignTree ID="211" ParentID="21" TreeDesc="SD13" IsSelected="1">

  <AssignDetail Assign="Rule4" Desc="Desc4" />

  </AssignTree>

  <AssignTree ID="212" ParentID="21" TreeDesc="SD14" IsSelected="1">

  <AssignDetail Assign="Rule10" Desc="Desc10" />

  </AssignTree>

  </ChildAssigns>

  </AssignTree>

  <AssignTree ID="22" ParentID="2" TreeDesc="SD12" IsSelected="0">

  <AssignDetail Assign="Rule1" Desc="Desc1" />

  </AssignTree>

  </ChildAssigns>

  </AssignTree>

  </AssignTrees>

  The 'IsSelected' column you see in the structure is not actually part of the table itself, I am using it to check whether an 'Assign' is assigned to 'OBJ1'. In the XML, the parent 'AssignTree' does not have a 'Rule' related to it directly. So if all child 'AssignTrees' of that 'ParentID' are assigned to 'OBJ1', its 'IsSelected' property should be 1. If none of the child are assigned to 'OBJ1', then 'IsSelected' = 0 and if only some are assigned, then 'IsSelected' = NULL. I am at a loss at how to achieve this.

  Any pointers will help. Thanks in advance. If the question needs more info, please let me know.

  My Query:

  SELECT @o_ResultXml = ISNULL(CONVERT(VARCHAR(MAX),

  (SELECT ATree.ID AS ID,

  ATree.ParentID AS ParentID,

  ATree.Assign AS Assign,

  ATree.TreeDesc AS [Desc],

  CASE

  WHEN EXISTS ( SELECT 1

  FROM ObjAssign obj WITH(NOLOCK)

  WHERE obj.[Object] = 'OBJ1'

  AND obj.Assign = ATree.Assign)

  THEN @TRUE

  ELSE @FALSE

  END AS IsSelected,

  (SELECT t.Assign AS Assign,

  t.[Desc] AS [Desc]

  FROM AssignDetails t WITH(NOLOCK)

  WHERE t.Assign = ATree.Assign

  FOR XML RAW('AssignDetail'), TYPE),

  dbo.udf_SelectChildAssigns(ATree.ID, 'OBJ1')

  AS ChildAssigns

  FROM AssignTree ATree

  WHERE ATree.ParentID IS NULL

  FOR XML RAW('AssignTree'), TYPE, ROOT('AssignTrees'))), '')

  Variables used here: @TRUE = 1 @FALSE = 0

  This function that you see called in the query, is basically the same query, but the Where clause goes like this: WHERE ATree.ParentID = @inputParentID

  The Case Statement is just for the child AssignTree that references a row in AssignDetails directly. A Parent AssignTree does not have an AssignDetails row associated with it. So I need to calculate its 'IsSelected' value from its children. That's the part I am not handling here (I don't know how).