Re: Watch out for this one :o)

Norman,

If I look in the Oracle8i SQL Reference, I find the following paragraph under the Aggregate Functions intro:

"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."

It would have been nice if they had put a "NOTE" or "CAUTION" statement on the paragraph so it stood out from the body text. Who knows, maybe it is flagged in the printed docs, but the on-line docs have it buried in body text, where it's easily overlooked (especially by old eyes like mine). ;-)

Roger Crowley - DBA - LearningFramework

Norman Dunbar wrote:
> 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.
>
> 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
> -------------------------------------
>
>
>
>
>
>
>
Received on Mon Jul 22 2002 - 11:27:09 CDT