The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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

asked 12 Sep '13, 08:01

guiguidili's gravatar image

guiguidili
1111
accept rate: 0%

edited 12 Sep '13, 08:48

Graeme%20Perrow's gravatar image

Graeme Perrow
8.3k369106

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

(12 Sep '13, 11:18) nico

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

(13 Sep '13, 03:43) guiguidili
Replies hidden

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

(13 Sep '13, 04:16) Volker Barth

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

(13 Sep '13, 05:18) guiguidili
1

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

(13 Sep '13, 09:19) Reimer Pods

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;
permanent link

answered 13 Sep '13, 14:43

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

edited 13 Sep '13, 15:21

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×12

question asked: 12 Sep '13, 08:01

question was seen: 1,509 times

last updated: 13 Sep '13, 15:21