Re: Oracle Precompilers and Nulls

First, this is not a precompiler "problem". It is an SQL issue. The same results are obtained in SQL*Plus if you join two tables on columns which allow nulls. The nulls are not evaluated in a join statement using "=" (as far as I know). Here are two possible alternatives.

1. Nvl( TNAME.CNAME, '?????' ) = Nvl( :hostvar, '?????' )

    where ????? is a value of your choice which should NOT be in     the range of CNAME for this to work properly. If I use this     type of command I try to pick some very strange value. But     be careful on dates and numbers (especially numbers since there     are fewer strange values). I personally would not use this     construct in very much (if any) production code. It works fine     for quick and dirty stuff.

2. ( ( TNAME.CNAME is NULL And :hostvar is NULL )

    Or
( TNAME.CNAME is Not NULL And :hostvar is Not NULL And
        TNAME.CNAME = :hostvar ) )

    I often use this construct (or a similar one) to handle optional     variables on a query. This should return "matches" on null     columns (i.e. where both the host variable and table.column are     NULL). It does not, however, return stuff is one of the two     columns is NULL and the other has a value. There are of course     ways to do this.

    Also, as I'm sure some people will point out, the "TNAME.CNAME is     Not NULL And..." line is optional. You could use:

    ( ( TNAME.CNAME is NULL And :hostvar is NULL )     Or
( TNAME.CNAME = :hostvar ) )

I hope this is of some redeeming value.

Mark McGregor

"Ancient definition of Oracle: A place where people queried pig livers  in order to make important decisions. What a marvelous history for  a database manager..." Received on Fri Sep 04 1992 - 20:41:35 CEST