You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cayenne.apache.org by Mike Kienenberger <mk...@gmail.com> on 2006/08/20 03:04:54 UTC
Nulls in oracle (ie, empty outer join records) cannot be compared
Apparently, life gets worse. Oracle makes it very difficult to
compare outer join record values.
Nulls do not equal anything, nor do they "not equal" anything.
http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html
So if I want to compare any column from a left outer join, I have to
explicitly handle the nulls somehow.
For example, use nvl to provide an alterate default value whenever a
null is found.
( (nvl(RTRIM(t4.IS_ACTIVE), 'N') <> 'Y')
Should something like this be automatically done for any outer join
implementation?
We can't use nvl genericly, but we can add another clause:
(x is null) or (x <> 'Y') for an inequality.
(x is not null) and (x = 'Y') for an equality
Apparently the behavior can vary from database to database and even
between join and search clauses for some databases. No end of fun.
Re: Nulls in oracle (ie, empty outer join records) cannot be compared
Posted by Andrus Adamchik <an...@objectstyle.org>.
Yeah, this is universal behavior, not specific to Oracle. In raw SQL
I usually use "nvl", but I guess until we support functions in
expressions, explicit null checking is required.
Andrus
On Aug 20, 2006, at 5:04 AM, Mike Kienenberger wrote:
> Apparently, life gets worse. Oracle makes it very difficult to
> compare outer join record values.
> Nulls do not equal anything, nor do they "not equal" anything.
>
> http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html
>
> So if I want to compare any column from a left outer join, I have to
> explicitly handle the nulls somehow.
>
> For example, use nvl to provide an alterate default value whenever a
> null is found.
>
> ( (nvl(RTRIM(t4.IS_ACTIVE), 'N') <> 'Y')
>
> Should something like this be automatically done for any outer join
> implementation?
>
> We can't use nvl genericly, but we can add another clause:
>
> (x is null) or (x <> 'Y') for an inequality.
> (x is not null) and (x = 'Y') for an equality
>
> Apparently the behavior can vary from database to database and even
> between join and search clauses for some databases. No end of fun.
>
Re: Nulls in oracle (ie, empty outer join records) cannot be compared
Posted by Mike Kienenberger <mk...@gmail.com>.
Ok. So I need to handle inequality differently. Equality doesn't
need to change since, if something is equal to a non-null value, then
it won't be equal to a null value.
So what other operator types need special handling? NOT_LIKE?
NOT_LIKE_IGNORE_CASE? NOT_BETWEEN?
Are these the only ones, or am I missing something?
On 8/19/06, Mike Kienenberger <mk...@gmail.com> wrote:
> Apparently, life gets worse. Oracle makes it very difficult to
> compare outer join record values.
> Nulls do not equal anything, nor do they "not equal" anything.
>
> http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html
>
> So if I want to compare any column from a left outer join, I have to
> explicitly handle the nulls somehow.
>
> For example, use nvl to provide an alterate default value whenever a
> null is found.
>
> ( (nvl(RTRIM(t4.IS_ACTIVE), 'N') <> 'Y')
>
> Should something like this be automatically done for any outer join
> implementation?
>
> We can't use nvl genericly, but we can add another clause:
>
> (x is null) or (x <> 'Y') for an inequality.
> (x is not null) and (x = 'Y') for an equality
>
> Apparently the behavior can vary from database to database and even
> between join and search clauses for some databases. No end of fun.
>