Re: SQLServer to Oracle

From: Mark D Powell <mark.powell_at_eds.com>
Date: 16 Aug 2001 06:21:09 -0700
Message-ID: <178d2795.0108160521.2a6b77e5@posting.google.com>

anu_chak_at_globalnoise.com (Anupriyo Chakravarti) wrote in message news:<c0fcc770.0108151112.20329432_at_posting.google.com>...
> This query works in MS SQL Server but not in Oracle 8. Any suggestions
> how to achieve the samething in Oracle?
>
> SELECT TPARENT.id, TPARENT.name
> (SELECT COUNT(*) FROM TPARENT, TCHILD WHERE TCHILD.parentid =
> TPARENT.id) AS numchildren
> FROM TPARENT
>
> Basically, I have two tables TPARENT and TCHILD. TCHILD has a FK
> parentid to TPARENT.id. I want a list of all parents and the number of
> children they have.
>
> Any insights would be appreciated.
>
> - Anupriyo

Well, I believe there is more than one way to do this. (By the way your post is missing a comma after TPARENT.name).

1 select h.order_no, d.numchildren
2 from oehead h

 3         ,( select b.order_no, count(*) as numchildren
 4            from   oedetl  b
 5            group by b.order_no
 6           ) d

7* where h.order_no = d.order_no

ORDER_NO NUMCHILDREN
---------- -----------

943716               3
943717               1
943718               1
943719               1
943720              11
943721              10
943722              14
943723              17
943724               4

Or in version 8.1.7 you can do what your tried

1 select h.order_no, (select count(*)

 2                       from   oedetl
 3                       where  oedetl.order_no = h.order_no
 4                      ) as numchildren

5* from oehead h

ORDER_NO NUMCHILDREN
---------- -----------

943714               2
943716               3
943717               1
943718               1
943719               1
943720              11
943721              10
943722              14
943723              17
943724               4

Mark D Powell --

Received on Thu Aug 16 2001 - 08:21:09 CDT