Re: group by problem (ora-00979)

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 16 Jul 2002 23:09:45 +0200
Message-ID: <uj93nmd6e0295a@corp.supernews.com>

"Shoval Tomer" <shoval_at_softov.co.il> wrote in message news:714a60c1.0207160409.4a3662b4_at_posting.google.com...
> trying to group by when the sub query has count(*) in it.
> This query works fine in SQL server, though.
>
> select s.sndnam as Sender, s.sndcode as SenderCode,
> (select count(*) from r rRes,o oRes,c cRes
> where rRes.rordno = oRes.oordno
> and rRes.rdate = oRes.odate and oRes.osndcod = s.sndcode
> and cRes.ccode = rRes.rtest and rRes.rresult='POSITIVE') as TotalPos
> from s , o , r , c
> where s.sndcode = o.osndcod and r.rordno = o.oordno and r.rdate =
o.odate
> and c.ccode = r.rtest
> group by s.sndnam, s.sndcode
>
>
>
> Thanks

This is not going to work at all, also you are referring directly to elements of the outer query in the *inline view*. (This is NOT a subquery, it is an inline view)
so it should be

select s.sndnam

       ,  s.sndcode
       ,  iv.counter

from
s,
(select o.sndcod

        , count(*) counter
 from o, r, c
where r.rordno = o.oordno

   and r.rdate = o.odate
   and c.ccode = r.rtest
   and r.rresult='POSITIVE'
group by o.sndcod) iv
where s.sndcod = iv.sndcod
/

Oracle is NOT sqlserver sold by a different vendor.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address

Received on Tue Jul 16 2002 - 16:09:45 CDT