Re: Why do Oracle treat NULL like this?

Interesting. As far as I know NULL is defined as unknown in Oracle and all aritmetical operations involving NULL will return NULL - which explains your 2nd example.

It seems like Oracle simply ignores NULL when concatenating strings. I agree it's not consistent with the "unknown" idea above. Maybe some other experts will know more about the subject.

Anyway, handling null values is always tricky, since it might result in unforseen results. I strongly recommend using NVL whenever there is possibility of null. So your examples could be written as:

select 'a' || nvl(null,' ') || 'b' from dual; select 1 + nvl(null,0) from dual;

Using NVL is especially important when using group functions, like MAX, AVG.

In article <BDrE4.21$tz6.579403_at_news1.van.metronet.ca>, "Michael" <MichaelChanFong_at_SierraSystems.com> wrote:
> Hi,
>
> select 'a' || NULL || 'b'
> from dual
> gives 'ab'
>
> but
>
> select 1 + NULL
> from dual
> gives NULL
>
> Why in the first example, NULL is considered as empty string, while in
the
> second example NULL is considered as undefined? That does not look
> consistent. What is the rationale behind it?
>
> Thanks in advance.
> Michael
>
>

--
If the human brain were so simple
that we could understand it,
we would be so simple we couldn't.
-Makes Sense... don't it?

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Mar 29 2000 - 14:04:29 CST