mario1fb wrote:

>Special thanks to Ali and Adomas for providing the SP idea.
>Below is working SP code, in case someone wanted to re-use it.
>One thing I did not manage to work out is how to get the IB SQRT UDF
>operational within the SP below.
>I tried to declare the code below within the SP code but that did not
>work.
> DECLARE EXTERNAL FUNCTION SQRT
> DOUBLE PRECISION
> RETURNS DOUBLE PRECISION BY VALUE
> ENTRY_POINT 'IB_UDF_sqrt' MODULE_NAME 'ib_udf';
>At the end, I had to apply Delphi SQRT built-in function to the SP
>output variable
>

I think,you should execute this ddl in sql editor, then commit, and udf
function would be available in any procedure/sql sentence.
No need (error) it to redeclare in SP.

Adomas

>.
>
>No doubt, someone will be able to provide a guidance on this last
>obstacle.
>
>Kind regards,
>Mario
>
>COMMIT WORK;
>SET AUTODDL OFF;
>SET TERM ^ ;
>
>/* Stored procedures */
>
>CREATE PROCEDURE "GETSTDDEV"
>(
> "FIRSTDATE" TIMESTAMP
>)
>RETURNS
>(
> "AVGINDEX" REAL,
> "COUNTINDEX" INTEGER,
> "STDDEV" REAL
>)
>AS
>BEGIN EXIT; END ^
>
>
>ALTER PROCEDURE "GETSTDDEV"
>(
> "FIRSTDATE" TIMESTAMP
>)
>RETURNS
>(
> "AVGINDEX" REAL,
> "COUNTINDEX" INTEGER,
> "STDDEV" REAL
>)
>AS
>Begin
> 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;
>End
> ^
>
>SET TERM ; ^
>COMMIT WORK;
>SET AUTODDL ON;
>
>Note: the SP output variable stddev must be placed inside the external
>sqrt function , e.g. in Delphi use: SQRT(stddev), in order to obtain
>the actual Standard Deviation.
>
>
>--- In firebird-support@yahoogroups.com, Ali Gökçen <alig@e...> wrote:
>
>
>>Hi,
>>
>>if i remember correctly, STDDEV is:
>>
>>if(count(x)>1)
>> sqrt ( sum((x-avg(x))*(x-avg(x))) / (count(x)-1) )
>>
>>Regards.
>>
>>Ali
>>
>>--- In firebird-support@yahoogroups.com, Adomas Urbanavicius
>><adomas@i...> wrote:
>>
>>
>>>I wonder if it is not possible to write in one select:
>>> (i dont remember exacty what is stddev, but if it is
>>>
>>>
>>SQRT
>>
>>
>>>(SUM( (x - mean(x) / N))
>>>simple idea :
>>>select
>>> sqrt ( sum (x - avg(x))/count(x) )
>>> from tb
>>>
>>>As nested agregations are not allowed,
>>>in sql realization would be something like
>>>
>>>select
>>> sqrt
>>> (
>>> sum
>>> (x - (select avg(x) from tb)/
>>> (select count(x) from tb)
>>> )
>>> )
>>> from tb
>>>Of course , this will be definetely slower than sample before in
>>>
>>>
>>sp, so
>>
>>
>>>final idea woul be :
>>>create sp ....
>>>select avg(x),count(x) from tb into avg_x,count_x
>>>select sqrt(sum (x - avg_x)/count_x) from tb into res;
>>>
>>>
>>>Ali G?n wrote:
>>>
>>>
>>>
>>>>Hey Mario,
>>>>why are you so pessimist? what is to be a programmer?
>>>>to be a drag-dropper? or visual screen graphic designer?
>>>>
>>>>if writing a STDDEV function using a procedure very difficult for
>>>>you, then how may you write more complex programs?
>>>>
>>>>what is STDDEViation?
>>>>
>>>>n=total=0
>>>>sampleavg = AVG(myfiled) -- use coalasce if there is null values
>>>>
>>>>for
>>>>select samplevalue from mytable into :myvalue
>>>>do
>>>>begin
>>>> n= n +1
>>>> total = total + (myvalue - sampleavg)*(myvalue - sampleavg);
>>>>end
>>>>stddev = sqrrootfunctionofUDF(total / (n-1))
>>>>
>>>>if there is no sqrroot function in UDF, (i never need,
>>>>so i dont now if exists) you can write one yourself using newton
>>>>rapson method for example...
>>>>
>>>>Regards.
>>>>
>>>>Ali
>>>>
>>>>
>
>
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>