samedi 18 juin 2016

SQL, OrgNode, Hierarchy

I have a problem: Table I:

SELECT OrgNode as 'hierarchyid'
, OrgLevel as "non-type '
, UniCODOBJ as 'uniqueidentifier'
, BitREQUIRED as "bit"
, StrField as 'nvarchar (50)'
, StrTITLE_FIELD as 'nvarchar (50)'
, IntCODE_LENGTH as 'int'
OF BOX_HIERARCHY

and I have the query:

DECLARE @CurrentNode hierarchyid
SELECT @ CurrentNode = OrgNode
OF BOX_HIERARCHY
where uniCODOBJ = 'CBC33732-2E4C-4103-887A-B968E53C0E05';

SELECT OrgNode, OrgNode.ToString ()
, OrgLevel
, uniCODOBJ
, bitREQUIRED
, strField
, strTITLE_FIELD
, intCODE_LENGTH
of BOX_HIERARCHY
where OrgNode.IsDescendantOf (@CurrentNode) = 1

but, this query returns:

OrgNode OrgNode.string OrgLevel BitRequired
0x78 / 3/1 1
0x7AC0 / 3/1/2 1
0x7AD6 / 3/1/1/3 0
0x7AD6B0 / 3/1/1/1/4 0
0x7AD6B580 / 3/1/1/1/1/5 1
0x7AD6B5AC / 3/1/1/1/1/1/6 1

because OrgNode -> 0x78 have uniCODOBJ: CBC33732-2E4C-4103-887A-B968E53C0E05

where is my problem? direct son is only necessary:

with OrgNode -> 0x78 only have 1 direct children

OrgNode OrgNode.string OrgLevel BitRequired

0x78 / 3/1 1
0x7AC0 / 3/1/2 1

with OrgNode -> 0x7AC0 with 3 Possible direct children OrgNode OrgNode.string OrgLevel BitRequired

0x7AC0 / 3/1/2 1
0x7AD6 / 3/1/1/3 0
0x7AD6B0 / 3/1/1/1/4 0
0x7AD6B580 / 3/1/1/1/1/5 1

my i'm interested to get the query with which to obtain all possible direct children but that query returns me all relatives children if I have to go through 3 levels as in the 0x7AD6B5AC case is at level 6, with 3 levels AT LEAST 0x78 and to make the consultation for the 0x78, it returns all me, which I do not care.

Aucun commentaire:

Enregistrer un commentaire