vendredi 17 juin 2016

Set Column value depending on column value of child rows

I need help with my SQL query. I have the following table structure: 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.ar_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

EDIT:

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).

Aucun commentaire:

Enregistrer un commentaire