Very Computer

When doing a simple compare  between 2 columns, UDB seems to be behaving
differently  if there is a NULL value (probably following the ANSI standard
for treating NULL values).

Eg:

If  tablex has the folowing 2 columns

a  b

1 1
1 2
NULL 3

The following query :

If  a != b
/* Do something */

in Sybase, would return the 2nd and 3rd rows, but in UDB, it would only
return the 2nd row. So is there a way in UDB to get the 2nd and 3rd row
(i.e: NULL should not be equal to 3).

I do know we can do a substitute value for NULL using COALASCE, but looking
for some config settings which can change this default NULL behavior.

thanks in advance.

No such configuration setting - it's the ANSI standard as you say.

Jeremy

Rovy Sze wrote on Wednesday 27 November 2002 20:46:

Quote:> When doing a simple compare  between 2 columns, UDB seems to be behaving
> differently  if there is a NULL value (probably following the ANSI standard
> for treating NULL values).

> Eg:

> If  tablex has the folowing 2 columns

> a  b

> 1 1
> 1 2
> NULL 3

> The following query :

> If  a != b
> /* Do something */

> in Sybase, would return the 2nd and 3rd rows, but in UDB, it would only
> return the 2nd row. So is there a way in UDB to get the 2nd and 3rd row
> (i.e: NULL should not be equal to 3).

So Sybase is not conforming to the SQL standard in this case.  To test for
NULL, you have to use the ... IS NULL or ... IS NOT NULL predicates.

Quote:> I do know we can do a substitute value for NULL using COALASCE, but looking
> for some config settings which can change this default NULL behavior.

You should rephrase the query to:

SELECT ...
FROM   ...
WHERE  a IS NULL AND b IS NOT NULL OR
       a IS NOT NULL AND b IS NULL OR
       a <> b

p.s: What you are showing is that NULL is _not_ a value.  One of the basic
properties of values is that they can be compared with other values.

--
Knut Stolze
DB2 Spatial Extender
IBM Germany / University of Jena