Re: min(); never no_data_found

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 23 Dec 2009 05:50:39 -0800 (PST)
Message-ID: <79bb2ee5-f28a-43b9-af1e-4597012495f0_at_a32g2000yqm.googlegroups.com>

On Dec 22, 4:15 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> On 22.12.2009 21:39, cate wrote:
>
>
>
>
>
> > On Dec 22, 2:37 pm, cate<catebekens..._at_yahoo.com>  wrote:
> >> How can you make min() throw no_data_found?
>
> >> select min(datex) from tablex where x = 1;
>
> >> With min(), I never get a no_data_found exception.
>
> > The check is useless because the IF condition is tested only when
> > %NOTFOUND is false. When PL/SQL raises NO_DATA_FOUND, normal execution
> > stops and control transfers to the exception-handling part of the
> > block.
>
> > However, a SELECT INTO statement that calls a SQL aggregate function
> > never raises NO_DATA_FOUND because aggregate functions always return a
> > value or a null. In such cases, %NOTFOUND yields FALSE, as the
> > following example shows:
>
> > BEGIN
> >     ...
> >     SELECT MAX(sal) INTO my_sal FROM emp WHERE deptno = my_deptno;
> >        -- never raises NO_DATA_FOUND
> >     IF SQL%NOTFOUND THEN  -- always tested but never true
> >        ...  -- this action is never taken
> >     END IF;
> > EXCEPTION
> >     WHEN NO_DATA_FOUND THEN ...  -- never invoked
>
> It is unclear for me, what a business goal are you behind? If you know,
> that aggregate functions always return value or null, why don't you
> write your code in such a manner, that this circumstance is accounted?
> But if you need by any means raise a no_data_found by aggregate
> functions, maybe that helps...
>
> SQL> declare
>    2  my_sal number;
>    3  my_deptno number := -1;
>    4  begin
>    5   select max(sal) into my_sal from emp where deptno = my_deptno
> group by 1;
>    6  exception
>    7   when no_data_found then
>    8   if sql%notfound then
>    9    dbms_output.put_line('tests should be done thoroughfull');
>   10   end if;
>   11   dbms_output.put_line('never say never');
>   12  end;
>   13  /
> tests should be done thoroughfull
> never say never
>
> PL/SQL procedure successfully completed.
>
> Best regards
>
> Maxim- Hide quoted text -
>
> - Show quoted text -

Cate, why not test for a NULL value being returned and if so the RAISE NO_DATA_FOUND?

 > _at_t20
 > set echo on
 > set serveroutput on

 1> declare
  2 v_variable varchar2(10);
  3 begin
  4 select 'X' into v_variable from dual;   5 if v_variable is null
  6 then RAISE NO_DATA_FOUND;
  7 end if;
  8 dbms_output.put_line('Found Data');   9 exception
 10 when NO_DATA_FOUND then dbms_output.put_line('NO DATA');  11 end;
 12 /
Found Data

PL/SQL procedure successfully completed.

 1 > declare
  2 v_variable varchar2(10);
  3 begin
  4 select NULL into v_variable from dual;   5 if v_variable is null
  6 then RAISE NO_DATA_FOUND;
  7 end if;
  8 dbms_output.put_line('Found Data');   9 exception
 10 when NO_DATA_FOUND then dbms_output.put_line('NO DATA');  11 end;
 12 /
NO DATA PL/SQL procedure successfully completed.

HTH -- Mark D Powell -- Received on Wed Dec 23 2009 - 07:50:39 CST