[Gambas-user] handling database nulls

[Stephen Bungay]

   OK, you  have nulls and need values... you could use COALESCE...

SELECT COALESCE(FieldName,0) AS FieldName FROM TableName;

   This will result in any NULL values being returned as zeros.
Hope this helps you out.

Steve.

nando wrote:
> What about testing if it's null first as in...
> 
> IF IsNull(dbresult!rangeheight) THEN
>   'it's null here, you might want to do something like
>    dp.rangeheight=0
> ELSE
>    dp.rangeheight=$dbresult!rangeheight
> 
> ENDIF
> 
> 
> ---------- Original Message -----------
> From: Bruce <bbruen at ...2090...>
> To: gambas-user at lists.sourceforge.net
> Sent: Wed, 15 Apr 2009 17:27:38 +1000
> Subject: [Gambas-user] handling database nulls
> 
>> This has sort of been asked before. However, the answers I have found haven't 
>> helped.
>>
>> My database has a LOT of integer fields, arranged into sets of fieldwork 
>> results.  The majority of them contain nulls, quite validly as the data for a 
>> particular set may not have been entered or even collected yet.
>>
>> My Gambas problem is creating the classes for the various plot types.  Within 
>> Gambas the data items are integers. When I try and read the existing datum 
>> point into the class (as in dp.rangeheight=$dbresult!rangeheight ) it is 
>> complaining that the value is a null not an integer, as in "wanted integer, 
>> got null instead").
>>
>> Now, coercing the value of the object data to zero is NOT valid, as in the 
>> above example, the rangeheight is null not zero, as zero means 0m above the 
>> basepoint.  Similarly, coercing it to a negative value wont work either, as 
>> that means meters below the base point.
>>
>> Is there any way to set the value of an integer variable to an "uninitalised" 
>> state?
>>
>> bruce
>>
>> ------------------------------------------------------------------------------
>> This SF.net email is sponsored by:
>> High Quality Requirements in a Collaborative Environment.
>> Download a free trial of Rational Requirements Composer Now!
>> http://p.sf.net/sfu/www-ibm-com
>> _______________________________________________
>> Gambas-user mailing list
>> Gambas-user at lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/gambas-user
> ------- End of Original Message -------
> 
> 
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by:
> High Quality Requirements in a Collaborative Environment.
> Download a free trial of Rational Requirements Composer Now!
> http://p.sf.net/sfu/www-ibm-com
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
>