autoexcrement (12/10/2010)
Fascinating article! I have a couple of naive questions about the following bit of G code:
Adjacency (NodeID, ParentID) as -- Adjacency Query
(select 0, ID, ParentID
from dbo.HierarchyHybrid
where ID = @NodeID_in
andexists
(select*
from dbo.HierarchyHybrid h2
where h2.TopParentID = HierarchyHybrid.TopParentID
and RangeStart is null)
1. Is it supposed to have the "select 0, " or is that just a typo? It looks like the surrounding code is expecting 2 values there, not 3. If not a typo, I don't understand the "0" part.
2. Why doesn't this work?
Adjacency (NodeID, ParentID) as -- Adjacency Query
(select 0, ID, ParentID
from dbo.HierarchyHybrid
where ID = @NodeID_in
and RangeStart is null)
Thanks, regardless of whether you have time to reply.
The 0 is meant to indicate the row that was from the original parameter value. This comes in handy if you are querying up and down the hierarchy and need to know which one you originally asked for.
The "and exists" subquery merely tests that the row requested is in a hierarchy that has a top level. The version you wrote tests that it IS the top level. There's a difference.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon