Re: Watch out for this one :o)

Niall Litchfield wrote:

> "Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> wrote in message
> news:E2F6A70FE45242488C865C3BC1245DA702670ADA_at_lnewton.leeds.lfs.co.uk...
> > 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 !
>
> If you run
> select sum(b)
> from test
> where a=4;
>
> you won't get no rows selected but NULL.
>
> So it seems to me that the *bug* is not in PL/SQL but SQL, given that surely
> the answer is zero. I don't see any circumstances under which it could be no
> data found though. P.S. At least in 9.2
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************

It isn't a bug. It is as designed. And has been this way for as long as I can remember.

Part of the solution is to define numeric columns as follows:

CREATE TABLE test
b NUMBER(10) DEFAULT 0);

the other part is to use NVL.

Daniel Morgan Received on Mon Jul 22 2002 - 12:57:35 CDT