Re: Why Does Group By Cause ORA-01403: no data found

Hi

The behaviour is different because when you group by, Oracle: (1) Make the groups
(2) Count for each group

So, the message does not mean: I found no *rows* The message means: I found no *groups* (differently of a select with no group by)

So I think it is correct.

In article
<5C666F29101FCBC5.374DE020E7A7DAD9.99F3F4FB5267F119_at_library-proxy.airnews.net
>, ghubert_at_netpath.net (Gene Hubert) wrote:

> I was converting a SQL Server procedure to Oracle and hit a problem
> with ORA-01403: no data found when I selected count(*) into a
> variable. Why does Oracle handle this differently when there is a
> group by in the select. The following examples illustrate.
>
> 1st case - without a group by
>
> SQL> create or replace procedure junk as
> 2 i pls_integer;
> 3 begin
> 4 select count(*) into i from dual where dummy is null;
> 5 dbms_output.put_line(to_char(i));
> 6 end;
> 7 /
>
> Procedure created.
>
> SQL> execute junk
> 0
> (this is just what i would expect)
>
> 2nd case - with a group by
>
> SQL> create or replace procedure junk as
> 2 i pls_integer;
> 3 begin
> 4 select count(*) into i from dual where dummy is null
> 5 group by dummy;
> 6 dbms_output.put_line(to_char(i));
> 7 end;
> 8 /
>
> Procedure created.
>
> SQL> execute junk
> begin junk; end;
>
> *
> ERROR at line 1:
> ORA-01403: no data found
>
> ------------------------------
> I'm using Oracle 8.0.3 on NT 4.0.
>
> Gene Hubert
> Qualex Inc.
> Durham, NC
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Nov 12 1998 - 12:16:30 CST