Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Location
    British Columbia
    Posts
    44

    Unanswered: SubQuery in Oracle

    I have no experience in Oracle, only SQL Server. In SQL Server I can do something like this

    Select
    Custname,
    TotalOrders = (Select Count(*) From Orders Where Orders.CustId = Customers.CustId)
    From Customers

    I have tried variations but I get the error "FROM keyword not found where expected". I know I can use Group by to get the results but I just want to know how to run a sub-query in Oracle.

    Thanks

  2. #2
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118
    In oracle this would look like:

    SELECT
    Custname,
    COUNT(o.*)
    FROM customers c, orders 0
    WHERE c.custid = o.custid
    GROUP BY custname

    Good luck!!

    Remi http://askremi.ora-0000.com
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

  3. #3
    Join Date
    Jan 2003
    Location
    British Columbia
    Posts
    44
    No. No. Thats not what I want. I knew I chose a bad example.

    Again, what is the syntax for a subquery in Oracle? Does it even support subqueries.

  4. #4
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118
    Sorry

    SELECT *
    FROM emp
    WHERE deptno = SELECT MAX(deptno) FROM dep

    Subquery is selective and belongs in the WHERE clause.

    Hope this works for you

    Remi

    http://askremi.ora-0000.com
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

  5. #5
    Join Date
    Jan 2003
    Location
    British Columbia
    Posts
    44
    Subquery is selective and belongs in the WHERE clause.
    In SQL Server a subquery can be used to retrieve data (ie add to the select list) and is not restricted to the Where clause.

    I guess Oracle does not support this feature (or maybe just our version 7.3), so I must try to find another way.

  6. #6
    Join Date
    Jan 2003
    Posts
    1

    Cool Re: SubQuery in Oracle

    Your Query in Oracle would be:

    SELECT Custname
    FROM Customers
    WHERE TotalOrders = (SELECT COUNT(*)
    FROM Orders
    WHERE Orders.CustId = Customers.CustId)

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by brucevde
    In SQL Server a subquery can be used to retrieve data (ie add to the select list) and is not restricted to the Where clause.

    I guess Oracle does not support this feature (or maybe just our version 7.3), so I must try to find another way.
    Yes, you can use a subquery anywhere in a SQL statement in Oracle too, but not in 7.3, which is very out of date.

    The syntax equivalent to your first example would be:

    Select
    Custname,
    (Select Count(*) From Orders Where Orders.CustId = Customers.CustId) totalorders
    From Customers

Posting Permissions

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