--- In ib-support@y..., "Ivan Prenosil" <prenosil@m...> wrote:

> > The query SELECT SUM(NUMERIC_FIELD_X) FROM TABLE_X WHERE ...

returns

> > NULL if no records are found.
>
> Unfortunately, this is how SQL committee designed it, so I doubt it

will change in future releases.

For my unpretentious opinion, it is one of the cases when SQL
standard is point at issue. Workaround is to use UDF(Sum) which
returns 0 if Sum is null. The same I think about syntax restriction on
using Order By in subqueries. Since First was included into FB syntax
Order By in subselect start to have sense (First 1) and it will allow
to simplify and greatly speed up many queries when used instead
Min/Max in joins. Compare finding of currency rates for dates of
documents in the query, for example:

Select ...,
(Select R.Rate From Rates R
Where R.Currency=D.Currency And R.DateRegistr=
(Select Max(R1.DateRegistr) From Rates R1
Where R1.Currency=R.Currency
And R1.DateRegistr<=D.DateRegistr
)
)
From Documents D ...

And

Select ...,
(Select First 1 R.Rate From Rates R
Where R.Currency=C.Currency And R.DateRegistr<=D.DateRerigistr
Order By R.DateRegistr Desc
)
From Documents D, ...


Emulation of this on SP increases speed up to 300 times. Why we should
check possible mistake on the syntax level, if exception "multiple
rows in singleton select" will be raised on run-time for erronously
query and nothing wrong will not happen? Just my 2 cents.

Best regards, Alexander V.Nevsky.