Re: Problems with NULL and UPDATE in OCI programs

Dave Mausner wrote:
>
> you cannot successfully use the form WHERE COLUMN=:X when the indicator on X
> declares it to be a null value. you can only compare to a null value by
> using the forms WHERE COLUMN IS NULL or WHERE COLUMN IS NOT NULL.
>

One way is to use a compound WHERE clause  WHERE (col=:X OR col IS NULL)
 WHERE (col=:X AND col IS NOT NULL)

Another way is to use multiple blocks
  if ( ind == NullVal )
   { SQL expressions 1 }
  else
   { SQL expressions 2 }

A third way is to dynamically build your statements based on the value of the indicator.

Other ways probably exist.

Each has it's own place and merits & has to be analyzed. If possible & I'm using PRO*C , I use SQL*Plus to 'test' whether I can use a variation of the first method to get the desired response.

All of this stems from the fact that a NULL is undefined (or as some say, improperly defined). In theory at least, one interpretation is that a NULL 'could' have a the value 'X' but we're not sure. Thus we have the explicit expression to state 'IS <NOT> NULL' to distinguish from a defined value.

/Hans Received on Thu Oct 03 1996 - 00:00:00 CEST