Hi Ali,
I have taken the UDF code below exactly as written from the IB UDF
library (see relevant LANGREF.PDF), and it works well with my STDDEV SP.

> > DECLARE EXTERNAL FUNCTION SQRT
> > DOUBLE PRECISION
> > RETURNS DOUBLE PRECISION BY VALUE
> > ENTRY_POINT 'IB_UDF_sqrt' MODULE_NAME 'ib_udf';

> did you send DOUBLE PRECISION to sqrt?
> REAL is NOT DOUBLE PRECISION(8 bytes float),
> it is single precision (4 bytes float).

I have tested your suggested "sql friendly new formula (one pass).":
select sqrt( count(x)*sum(x*x)- sum(x)*sum(x) ) / count(x)
from mytable
where mydate>=?

and unfortunately, it produces different results than the original
STDDEV formula (both with division by count(x) and by count(x)-1).

Since I also need to obtain the avg(x), the double select statement in
SP works well for me.
Thanks again for all your suggestions.
Mario

>
>
> > select avg(xindex), count(xindex) from xtable
> > where xDate > :FirstDate
> > into :avgindex,:countindex;
> > if (:countindex > 1) then
> > select (sum((xindex - :avgindex)*(xindex - :avgindex)) /
> > (:countindex - 1)) from xtable
> > where xDate > :FirstDate
> > into :stddev;
>
> be careful here please,
> you are doing 2 selects.
> if your db on heavy load(too much users and inserts), then there may
> be some commits between these 2 selects, and you may get some
> incorrect stddev result.
> (if your transaction is not in snapshot isolation level)
> use snaphot transaction for this operation or
> use single select operation as i write in previous mail or
> eliminate the usage of new records via ID filtering or
> etc etc..
>
> Regards.
> Ali