Arno Brinkman wrote:

>>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?
>
> MySQL should also return NULL with MAX() if all values found have the state
> NULL or no value is found if they follow the SQL standard.
> If you're using Firebird 1.5 you can use COALESCE or CASE else use a UDF
> (function INVL(..) IIRC)
>
> INSERT INTO WHATEVER (NUMBER, USER)
> SELECT (COALESCE(MAX(NUMBER), 0) + 1) AS NUMBER,
> "a name" AS USER FROM WHATEVER

If you're using Firebird 1.0, you can use some UDF. There is Z() UDF in
rfunc library that does it, something like:

select (z(max(number)) + 1) as ...

HTH

--
Milan Babuskov
http://fbexport.sourceforge.net