|
Hello, I would like to create two functions, the function of distribution of the standard normal law and its inverse. I found a lot of algorithms on the internet that I translated in ASA. For the first one its working with a precision of 10^-6. But on the inverse, i got some troubles around the values 0.025 – 0.07 this is what I found 0.0250,-0.1519655042318483 0.0275,-0.18157811781753155 0.0300,-0.21447930730620093 0.0400,-0.37716271698223375 0.0500,-0.5746931267622849 0.0600,-0.7733235173347981 0.0700,-0.9385204315501925 It is totally wrong and I don’t know why. Maybe you could help me… This is my source code : ALTER FUNCTION "explore_0"."GET_NORMSINV_CALC"( @p double ) RETURNS double DETERMINISTIC BEGIN declare @x double;--x is the final result declare @a1 double; declare @a2 double; declare @a3 double; declare @a4 double; declare @a5 double; declare @a6 double; declare @b1 double; declare @b2 double; declare @b3 double; declare @b4 double; declare @b5 double; declare @c1 double; declare @c2 double; declare @c3 double; declare @c4 double; declare @c5 double; declare @c6 double; declare @d1 double; declare @d2 double; declare @d3 double; declare @d4 double; declare @p_low double; declare @p_high double; declare @q double; declare @r double; select @a1 = -3.969683028665376e+01,@a2 = 2.209460984245205e+02, @a3 = -2.759285104469687e+02, @a4 = 1.383577518672690e+02, @a5 = -3.066479806614716e+01, @a6 = 2.506628277459239e+00, @b1 = 1.330274429, @b2 = 1.615858368580409e+02, @b3=-1.556989798598866e+02, @b4=6.680131188771972e+01, @b5=-1.328068155288572e+01; select @c1=-7.784894002430293e-03, @c2=-3.223964580411365e-01, @c3=-2.400758277161838e+00, @c4=-2.549732539343734e+00, @c5=4.374664141464968e+00, @c6=2.938163982698783e+00, @d1=7.784695709041462e-03, @d2=3.224671290700398e-01, @d3=2.445134137142996e+00, @d4=3.754408661907416e+00; select @p_low = 0.02425; select @p_high = 1 - @p_low; if 0 < @p and @p < @p_low then select @q = sqrt(-2*log(@p)); select @x = (((((@c1*@q+@c2)*@q+@c3)*@q+@c4)*@q+@c5)*@q+@c6)/((((@d1*@q+@d2)*@q+@d3)*@q+@d4)*@q+1); end if; if @p_low <= @p and @p <= @p_high then select @q = @p - 0.5; select @r = @q*@q; select @x = (((((@a1*@r+@a2)*@r+@a3)*@r+@a4)*@r+@a5)*@r+@a6)*@q /(((((@b1*@r+@b2)*@r+@b3)*@r+@b4)*@r+@b5)*@r+1); end if; if @p_high < @p and @p < 1 then select @q = sqrt(-2*log(1-@p)); select @x = -(((((@c1*@q+@c2)*@q+@c3)*@q+@c4)*@q+@c5)*@q+@c6) /((((@d1*@q+@d2)*@q+@d3)*@q+@d4)*@q+1); end if; RETURN @x; END |
Your primary issue is that your constant value for @b1 is incorrect. It should be: @b1 = -5.447609879822406e+01 Your other issue is that you are mixing SQL language dialects in SQL Anywhere. You can either use T-SQL or Watcom SQL. Assuming that you are really trying to use T-SQL syntax, here is how you would write the function: create function "GET_NORMSINV_CALC"( @p double ) returns double deterministic as begin declare @a1 double declare @a2 double declare @a3 double declare @a4 double declare @a5 double declare @a6 double declare @b1 double declare @b2 double declare @b3 double declare @b4 double declare @b5 double declare @c1 double declare @c2 double declare @c3 double declare @c4 double declare @c5 double declare @c6 double declare @d1 double declare @d2 double declare @d3 double declare @d4 double declare @p_low double declare @p_high double declare @q double declare @r double select @a1 = -3.969683028665376e+01,@a2 = 2.209460984245205e+02, @a3 = -2.759285104469687e+02, @a4 = 1.383577518672690e+02, @a5 = -3.066479806614716e+01, @a6 = 2.506628277459239e+00, @b1 = -5.447609879822406e+01, @b2 = 1.615858368580409e+02, @b3=-1.556989798598866e+02, @b4=6.680131188771972e+01, @b5=-1.328068155288572e+01 select @c1=-7.784894002430293e-03, @c2=-3.223964580411365e-01, @c3=-2.400758277161838e+00, @c4=-2.549732539343734e+00, @c5=4.374664141464968e+00, @c6=2.938163982698783e+00, @d1=7.784695709041462e-03, @d2=3.224671290700398e-01, @d3=2.445134137142996e+00, @d4=3.754408661907416e+00 select @p_low = 0.02425 select @p_high = 1 - @p_low if 0 < @p and @p < @p_low begin select @q = sqrt(-2*log(@p)) return (((((@c1*@q+@c2)*@q+@c3)*@q+@c4)*@q+@c5)*@q+@c6)/((((@d1*@q+@d2)*@q+@d3)*@q+@d4)*@q+1) end if @p_high < @p and @p < 1 begin select @q = sqrt(-2*log(1-@p)) return -(((((@c1*@q+@c2)*@q+@c3)*@q+@c4)*@q+@c5)*@q+@c6) /((((@d1*@q+@d2)*@q+@d3)*@q+@d4)*@q+1) end select @q = @p - 0.5 select @r = @q*@q return (((((@a1*@r+@a2)*@r+@a3)*@r+@a4)*@r+@a5)*@r+@a6)*@q /(((((@b1*@r+@b2)*@r+@b3)*@r+@b4)*@r+@b5)*@r+1) end; |
Are you sure you have "select" in your code instead of "set" ??
Yes I am, this code is working fine, excepted for some values. And I don't know why ...
"SELECT @x = " does work as this is a T-SQL statement (though T-SQL doesn't have semicolons as statement delimiter...).
Are you aware of the builtin OLAP functions for statistical purposes - available since version 9.0.1?
Yes, I saw it but in my opinion, it's useless for the calculation of the distribution of a normal law and its invere
Just für fun I pasted the code into ISQL 12.0.1.3851, replacing ALTER with CREATE. Trying to run the statement I get "Syntax error at 'a1' line 32, because your code mixes Watcom (Function declaration without keyword AS, statements separated by ';') with T-SQL snytax (SELECT @a1= ...).
So it looks pretty much like it's not possible to run your code (even if I don't understand what it's supposed to do).