B
bismuth83
I'd like to have a recursive query that refers to itself, but I have
run into the 'circular reference' error. Is there a workaround
that'll let me do something similar?
As a test for a larger version, I have a table [TestRecurse] which
works as a nested set (see http://www.intelligententerprise.com/001020/celko.jhtml?_requestedid=697912):
ListID Value lft rgt
1 0 1 18
2 0 2 9
3 0 10 17
4 1 3 4
5 2 5 6
6 3 7 8
7 0.4 11 12
8 0.5 13 14
9 10 15 16
I also use a query [TestRecurse_ListedChildren] based on this table
which lists any parents and their children:
ParentID ChildID
1 2
1 3
2 4
2 5
2 6
3 7
3 8
Then the circular reference query [TestRecurse_Formula] below:
SELECT
TestRecurse.ListID AS CurrentID,
TestRecurse_ListedChildren.ChildID,
nz(
(SELECT Count(TestRecurse_ListedChildren.ChildID) AS TotalChildren
FROM TestRecurse_ListedChildren
WHERE TestRecurse_ListedChildren.ParentID=[TestRecurse].[ListID]
GROUP BY TestRecurse_ListedChildren.ParentID)
,0) AS NumChildren,
Sum(
IIf(
[NumChildren]=0,
[TestRecurse].[Value],
(
SELECT top 1 Result
FROM TestRecurse_Formula
WHERE CurrentID=TestRecurse_ListedChildren.ChildID
)
)
) AS InitVal,
[InitVal]*3 AS Result
FROM
TestRecurse
LEFT JOIN TestRecurse_ListedChildren
ON TestRecurse.ListID = TestRecurse_ListedChildren.ParentID
GROUP BY
TestRecurse.ListID,
TestRecurse_ListedChildren.ChildID;
Circular reference is caused by the 3rd argument of the iff()
statement. But I think my joins are wrong as well, since the query
above would have duplicating parents. I've removed the duplication
and listed ideal results below:
CurrentID NumChildren InitVal Result
1 2 50.7 456.3
2 3 18 54
3 3 32.7 98.1
4 0 1 3
5 0 2 6
6 0 3 9
7 0 0.4 1.2
8 0 0.5 1.5
9 0 10 30
Hopefully that's not too confusing. Hope anyone can lend some ideas.
Thanks!
run into the 'circular reference' error. Is there a workaround
that'll let me do something similar?
As a test for a larger version, I have a table [TestRecurse] which
works as a nested set (see http://www.intelligententerprise.com/001020/celko.jhtml?_requestedid=697912):
ListID Value lft rgt
1 0 1 18
2 0 2 9
3 0 10 17
4 1 3 4
5 2 5 6
6 3 7 8
7 0.4 11 12
8 0.5 13 14
9 10 15 16
I also use a query [TestRecurse_ListedChildren] based on this table
which lists any parents and their children:
ParentID ChildID
1 2
1 3
2 4
2 5
2 6
3 7
3 8
Then the circular reference query [TestRecurse_Formula] below:
SELECT
TestRecurse.ListID AS CurrentID,
TestRecurse_ListedChildren.ChildID,
nz(
(SELECT Count(TestRecurse_ListedChildren.ChildID) AS TotalChildren
FROM TestRecurse_ListedChildren
WHERE TestRecurse_ListedChildren.ParentID=[TestRecurse].[ListID]
GROUP BY TestRecurse_ListedChildren.ParentID)
,0) AS NumChildren,
Sum(
IIf(
[NumChildren]=0,
[TestRecurse].[Value],
(
SELECT top 1 Result
FROM TestRecurse_Formula
WHERE CurrentID=TestRecurse_ListedChildren.ChildID
)
)
) AS InitVal,
[InitVal]*3 AS Result
FROM
TestRecurse
LEFT JOIN TestRecurse_ListedChildren
ON TestRecurse.ListID = TestRecurse_ListedChildren.ParentID
GROUP BY
TestRecurse.ListID,
TestRecurse_ListedChildren.ChildID;
Circular reference is caused by the 3rd argument of the iff()
statement. But I think my joins are wrong as well, since the query
above would have duplicating parents. I've removed the duplication
and listed ideal results below:
CurrentID NumChildren InitVal Result
1 2 50.7 456.3
2 3 18 54
3 3 32.7 98.1
4 0 1 3
5 0 2 6
6 0 3 9
7 0 0.4 1.2
8 0 0.5 1.5
9 0 10 30
Hopefully that's not too confusing. Hope anyone can lend some ideas.
Thanks!