Hi Marc,
NULL is a state meaning 'unknown', not a value. Next to my computer I
have my wallet which you do not know how much contains. Now, I am
adding 1 Norwegian krone into my wallet. How much does it contain?
Most people would say they still did not know, though maybe someone
would assume I was broke two minutes ago and that my wallet now
contained 1 krone (they would be wrong, hence MySQL must be wrong ;o).

The lesson for you to learn is that the value 0 is very different from
the state of NULL. If you mean 0, then you simply store 0 in your
tables. This may be done by having a before insert and before update
trigger on your field which checks for NULL and replaces it by 0 if
that is what you want.

Set

--- In firebird-support@yahoogroups.com, "Marc Geldon" wrote:
> Hello!
>
> I have a problem with "NULL". If I run a statement like SELECT
CAST(NULL AS INTEGER)+1 AS NUMMER FROM WHATEVER the result is NULL!
>
> That's a problem. I had a SQL which was running on mySQL. Some sort
of:
>
> INSERT INTO WHATEVER (NUMBER, USER)
> SELECT (MAX(NUMBER)+1) AS NUMBER,
> "a name" AS USER FROM WHATEVER
>
> In mySQL it worked very well. In FB not! He tells me something about
datatype not known and so on. Also a CAST AS INTEGER does not work.
How can I convert this NULL into a 0?
>
> Please help!