Inverse of the distribution function of the standard normal law

[Former Member]

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

[Former Member]

Are you sure you have "select" in your code instead of "set" ??

[Former Member]

Yes I am, this code is working fine, excepted for some values. And I don't know why ...

[VolkerBarth]

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

[Former Member]

Yes, I saw it but in my opinion, it's useless for the calculation of the distribution of a normal law and its invere

[reimer_pods]

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

[jeff_albion]

I found a lot of algorithms on the internet that I translated in ASA.

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;