Subquery syntax problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Subquery syntax problem

  1. #1
    Join Date
    Oct 2000
    Posts
    3
    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

  2. #2
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    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

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width