Subject Re: [firebird-support] Why NULL <> NULL ?
Author Kjell Rilbe
jasajona wrote:

> In trigger I have:
> --
> if (field1 = field1) then ...
> --
> when field1 = null, field2 = null
> (field1 = field2) = false :(
> so I have to make an exception for null case, that is very ilogical.

Your mistake is to think of null as "nothing". Null means "unknown".

Think about it if both field1 and field2 are "unknown", then (field1 =
field2) also has to be unknown (null), right? But when an unknown (null)
expression is used in a context where it has to be interpreted as true
or false, as in a where clause, null will be interpreted as false.

Try this:

if coalesce(field1, '') = coalesce(field2, '') then ...

Also, consider whether you really want to store null in these fields.
Maybe you should store empty strings, -1 or some other kind of marker
for "empty" instead of null?

Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64