RE: Problem with NULL values
Hi !
> Dear friends!
>
> I have some problem with my Query (SQL). It's about NULL values.
>
> SELECT ordernro, quantity, sent, (quantity - sent ) as 'q_left'
> FROM orders>
> So, If sent = NULL then (quatity - sent) is not "quantity". It'll become
> NULL too. So, I need the real value for "q_left".
>
For it proofed to be best to just eliminate Null Values first by a small SPL
and then to the callculations, sorts, groups etc.
Here are 3 simple functions doing this a somewhat canonical way:
CREATE FUNCTION eliminateNull ( tmpString VARCHAR(255,0) )
RETURNING VARCHAR(255, 0); If tmpString Is Null Then
RETURN '';
Else
RETURN tmpString;
End If
END FUNCTION;
CREATE FUNCTION eliminateNull ( tmpInt INT )
RETURNING INT; If tmpInt Is Null Then
RETURN 0;
Else
RETURN tmpInt;
End If
END FUNCTION;
CREATE FUNCTION eliminateNull ( tmpMoney MONEY )
RETURNING MONEY; If tmpMoney Is Null Then
RETURN 0;
Else
RETURN tmpMoney;
End If
END FUNCTION;
Then your query should do as expected if you use
SELECT ordernro, quantity, sent,
(quantity - eliminateNull(sent) ) as 'q_left'
FROM orders
cu Thomas
--
************************************************************
Thomas von Siebenthal Voice ++41 (0)32 / 645 04 70
Flurstrasse 28A Fax ++41 (0)32 / 645 04 74
CH-2544 Bettlach E-Mail Thomas@von.Siebenthal.ch
Switzerland WWW http://www.siebenthal.ch
************************************************************