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

Interesting why the store procedure compiles. From what I know, when using a group by clause, the column you are group by must be a select column.

Minh

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
Received on Wed Nov 11 1998 - 22:35:27 CST