| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: invalid rowid when pl/sql cursor has subquery with group function
A copy of this was sent to ssharma_at_clearnet.com (if that email address didn't require changing) On Wed, 01 Apr 1998 18:58:12 -0600, you wrote:
>Hello.
>Here's the simplest query to duplicate my problem
>
>declare
>cursor a is select hell from
> (select hell, count(*) cnt from hell_below group by hell)
> where cnt > 1;
>hell_rec number;
>begin
>open a;
>fetch a into hell_rec;
>end;
>
[snip]
>
>
>Any way around this? any easier way to find duplicates
simplest workaround:
select hell
from hell_below
group by hell
having count(*) > 1;
having is like WHERE but it allows access to aggregates after they have been computed.
> (select hell, count(*) cnt from hell_below group by hell)
> where cnt > 1;
>(please don't suggest self - joins, I tried and that takes far
> too long. This runs much, much faster in sql - plus)
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Apr 02 1998 - 00:00:00 CST
 |
 |