At 10:52 AM 22/04/2006, you wrote:

>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?

No, that's not the source of the dichotomy. It is a whole lot more
complicated than that, having more to do with the age of the database
engines, ongoing support for legacy implementation errors, who copied
whom when there were no standards, etc., etc., and that's a gross
understatement the whole situation.

>In Oracle: select NVL(sum(detail.amount), 0)

As in Firebird 1.0, where NVL() was available as a UDF. But 1.5
deprecated NVL() by introducing the internal functions CASE, COALESCE
and NULLIF. fB 2.0 also allows IIF.

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

In Standard SQL, Firebird and (I think) Oracle,
COALESCE(sum(detail.amount, 0). AFAIR, SQLServer also supports a
variety of COALESCE. But "standard SQL" and "MSSQL" are not known
for a high degree of intersection...

>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.

Well, it's fairly pointless to keep a dog and then bark at the
postman yourself.

./heLen