Re: NULL values in arithmetic

"Matt Pryor" <matt_at_ddm-net.com> wrote in message news:965294019.687690_at_lnd-hst-1.atlas.net.uk...
> Great, thanks I'll give that a try.
>
> Still don't understand the logic behind "nothing plus something = nothing"
> though!
>
>
>
>
> Klaus Zeuch <KZeuch_at_nospamhotmail.com> wrote in message
> news:8m9skk$oa4$18$1_at_news.t-online.com...
> > Hi,
> >
> > that's normal - nothing plus anything is nothing...

<snip>

That's because the logic logic isn't nothing plus anything equals nothing. because NULL != NOTHING.. Null means unknown value. So what your decode workaround is saying is 'where I don't know a value I shall assume it to be zero'. In many cases this will be a good assumption of course, but it is still that an assumption. If your null record means I haven't recorded this value yet, but there may be a value then the work-around is IMO wrong - it is logically equivalent to

select (15+nvl(NULL,8)+8+4) from emp;

and adding eight to the calculation for every null. Most people will reject this as a crazy workaround, but still be happy with substituting 0 for NULL.

--
Niall Litchfield
Oracle DBA
Audit Commission UK