Re: Nulls in oracle (ie, empty outer join records) cannot be compared

From: Mike Kienenberger (mkienen..mail.com)
Date: Tue Aug 22 2006 - 11:56:35 EDT

  • Next message: Mike Kienenberger: "Building null-checking expression without calling objectNode() twice?"

    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 <mkienen..mail.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.
    >

    This archive was generated by hypermail 2.0.0 : Tue Aug 22 2006 - 11:57:01 EDT