Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Watch out for this one :o)

Re: Watch out for this one :o)

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 22 Jul 2002 10:03:57 -0700
Message-ID: <ahhdtt06q6@drn.newsguy.com>


In article <E2F6A70FE45242488C865C3BC1245DA702670ADA_at_lnewton.leeds.lfs.co.uk>, Norman says...
>
>I've just been bitten by the following, and I can't find it in the docs,
>so maybe it is an 'undocumented feature', but it is most certainly on my
>list of gotchas now !
>
>CREATE TABLE TEST(A NUMBER, B NUMBER);
>INSERT INTO TEST VALUES (1,1);
>COMMIT;
>
>CREATE OR REPLACE FUNCTION tester(in_a IN NUMBER) RETURN NUMBER
>AS
>BEGIN
> total_amt NUMBER := 0;
>
> select sum(b) into total_amt
> from test
> where a = in_a);
>
> return (total_amt);
>
>EXCEPTION
> WHEN no_data_found THEN
> return (-1);
>END;
>/
>
>select tester(1) from dual; returns 1 as expected.
>select tester(4) from dual; returns NULL and not -1 as expected.
>
>It appears that aggregate functions don't fire off the EXCEPTION when
>nothing is returned from the table.
>Same happens in 8174 and 7134 which is all I've tested.
>
>
>If I remove the SUM() part, and just return the 'b' column, then the
>exception is fired for the second test.
>
>Nothing on Metalink, Google or in the DOcs - as far as I can see.

it is the defined behaviour of an aggregate without a GROUP BY.

The implied group by here is "group by the result set" -- there is one group.

Consider this:

select count(*)
  from t
 where x = 5;

Now, that should return 0 if x=5 is never satisfied right? Not raise NO_DATA_FOUND if there is no rows such that x=5. People would be very mad if we raised "no data found" for that one.

The same applies for

select count(*), sum(x)
  from t
 where x = 5;

You would expect that to return count(*) = 0 and sum(x) is NULL (no data).

Now, if you simply add a group by:

select count(*)
  from t
 where x = 5
 GROUP BY X; That'll raise no data found as their is nothing to actually group by.

I guess the existence (or lack thereof) of a note is because it would be an "anti note" -- the behavior in SQL is defined this way...

The documentation actually does state this behaviour:

...
All aggregate functions except COUNT(*) and GROUPING ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT never returns null, but returns either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.
.......

"if the data set contains no rows"

>
>Cheers,
>Norman.
>
>-------------------------------------
>Norman Dunbar
>Database/Unix administrator
>Lynx Financial Systems Ltd.
>mailto:Norman.Dunbar_at_LFS.co.uk
>Tel: 0113 289 6265
>Fax: 0113 289 3146
>URL: http://www.Lynx-FS.com
>-------------------------------------
>
>
>
>
>
>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Mon Jul 22 2002 - 12:03:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US