-
I am trying to work with some example code. The tutorial indicates that the following SQL will compile and execute in SQL Server. I'm working in Oracle 8.0.5 and can't get it to work. My first impression is that the syntax for the subquery is invalid; I've never seen a subquery that isn't in a WHERE clause.
SELECT Parent.category_id, Parent.category_name,
(SELECT Count(*) FROM category_tree Child2,
category_tree Parent2
WHERE
Child2.StartBranch BETWEEN Parent2.StartBranch AND Parent2.EndBranch
AND Child2.category_id = Parent.category_id) TreeBranch
FROM category_tree Child, category_tree Parent
WHERE (Child.StartBranch BETWEEN Parent.StartBranch AND Parent.EndBranch)
AND (Child.category_id=2)
ORDER BY TreeBranch
-
Try this:
SELECT Parent.category_id, Parent.category_name, TreeBranch.ccount
FROM
category_tree Child,
category_tree Parent,
(SELECT COUNT(*) ccount
FROM
category_tree Child2,
category_tree Parent2
WHERE
Child2.StartBranch BETWEEN Parent2.StartBranch AND Parent2.EndBranch AND
Child2.category_id = Parent.category_id) TreeBranch
WHERE
child category_id = treebranch.category_id AND
(Child.StartBranch BETWEEN Parent.StartBranch AND Parent.EndBranch) AND
(Child.category_id=2)
ORDER BY TreeBranch
-
Your query will work in 8i (at least I'm sure about 8.1.6, not so certain about 8.1.5), but not in 8.0. BTW, subquery can now be anywhere in the select clause, not only in WHERE - it can also be in FROM clause or in select list, as in your case.
