Helen Borrie wrote:

>> ...
>> total = 0;
>> select sum(...) form other_table into :total;
>> ...
>>
>> Again, if i understood Ms Helen Borrie's message correctly, if the
>> select returns NULL, the variable total will not be affected, so it will
>> be still be zero.
>
> No, you didn't understand correctly. The other message was in regard
> to what is returned to a variable if there are no matching rows. The
> poster (don't know if was you) thought that an "empty set" was "a row
> of nulls" so that null would be passed into the variables. As I
> pointed out, there is no such thing.
>
> Delphi programmers (particularly) develop this misconception and it's
> pretty hard to shake some of them out of it.
>
>> Today (at 5 AM, duh), i encountered another case with these lines :
>> ...
>> total = 0;
>> select sum(detail.amount)
>> from master
>> join detail on (master_id = detail_id
>> where (master.trans_date < :end_date) and
>> (detail.item_id = :item_id)
>> into :total;
>> ...
>>
>> Since the tables are empty, the result of the variable TOTAL is always
>> NULL inside this stored procedure (Firebird 1.5.3, Win32). This kinda
>> contradicts my conclusion above (about how a SELECT INTO that returns a
>> NULL will not affect the variable value).
>
> Yes, it's a perfect example of how you need to think, in order you
> shake off your belief that "a state of non-existence must be
> something we can measure and cut". To be fair to Delphi programmers
> everywhere, many of us cut our Delphi DB programming milk teeth on
> Paradox which doesn't support null. The BDE (which is the Paradox
> engine) returns a result set consisting of a single row of "zero-ed"
> values (0 for numerical values, empty string for strings). That
> works for Paradox, but it's an absurdity that we don't have to
> contend with in Firebird.
>
> ./heLen
>

MS Access would also return 0 - desktop databases v sql databases?

In Oracle: select NVL(sum(detail.amount), 0)
In MS SQLServer: select ISNULL(sum(detail.amount), 0)

If you want 0 from the database where the value might be null, tell it
to handle the possibility, otherwise check the value in the application
code.

regards
ian