At 01:03 PM 14/04/2003 +0200, you wrote:

>Hi,
>
>One of my stored procedures is manipulating a lot of tables and need in a
>loop the data that is writen to the db in this same loop.
>The problem I have is that the variables aren't update always when I need to
>retrive a null from the database.
>The current sollution I have is to set the var to 0 and then it works. Here
>follows an example
>StockQty = 0;
> StockAdaptionQty = 0;
> SELECT Stock FROM RESM_STOCK WHERE ItemIdx = :ItemIdx INTO
>:StockQty;
> SELECT Quantity FROM RESM_STOCK_ADAPTION_DETAILS WHERE
>ItemIdx = :ItemIdx AND AdaptionIdx =:AdaptionIdx INTO :StockAdaptionQty;
>
>so if i dont set StockQty and StockAdaptionQty to 0 then I don't get the
>correct data.
>
>Mayby this is a bug in FB or is there any other explination for it.

No, it's correct SQL standard behaviour with NULL. "Null is a state, not a
value". Null is not the same as zero (nor any other *value*) - it means
that the data item has an unknown value. If you don't understand nulls,
get hold of the Firebird Quickstart Guide from www.ibphoenix.com and get
acquainted with them. :-)

If you mean for null to be zero, then write Before Insert and Before Update
triggers to enforce zero on the nulls concerned.

In Fb 1.0.x, you can use the iNVL() function from the FBUDF library to
force a value into null output columns. In Fb 1.5 you will have CASE and
COALESCE built-in functions to achieve this.

heLen