SQLTeam.com | Weblogs | Forums

Convert to MS SQL

#1

I'm new to MS SQL.

Can someone kindly assist me with converting the below two PLsql queries (NORM DIST & NORMINV) to MS SQL?

1st --> NORM DIST

CREATE OR REPLACE FUNCTION ATOM.GetNORMSDIST(ip_DistributionAt Number)
RETURN Number AS
b1 Number := 0.319381530;
b2 Number := -0.356563782;
b3 Number := 1.781477937;
b4 Number := -1.821255978;
b5 Number := 1.330274429;
p Number := 0.2316419;
c Number := 0.39894228;
t Number;
x Number := -10.1;
result Number;

BEGIN
      if ip_DistributionAt >= 0.0 then
        t := 1.0 / ( 1.0 + p * ip_DistributionAt );
        result := (1.0 - c * exp( -ip_DistributionAt * ip_DistributionAt / 2.0 ) * t * ( t *( t * ( t * ( t * b5 + b4 ) + b3 ) + b2 ) + b1 ));
      else
        t := 1.0 / ( 1.0 - p * ip_DistributionAt );
        result := ( c * exp( -ip_DistributionAt * ip_DistributionAt / 2.0 ) * t * ( t *( t * ( t * ( t * b5 + b4 ) + b3 ) + b2 ) + b1 ));
      end if;
      
    Return result;

END;

2nd ---> NORMINV

CREATE OR REPLACE FUNCTION ATOM.GetNORMSINV(ip_Probability Number)
RETURN Number AS
result number;
a1 Number := -39.6968302866538;
a2 Number := 220.946098424521;
a3 Number := -275.928510446969;
a4 Number := 138.357751867269;
a5 Number := -30.6647980661472;
a6 Number := 2.50662827745924;
b1 Number := -54.4760987982241;
b2 Number := 161.585836858041;
b3 Number := -155.698979859887;
b4 Number := 66.8013118877197;
b5 Number := -13.2806815528857;
c1 Number := -7.78489400243029E-03;
c2 Number := -0.322396458041136;
c3 Number := -2.40075827716184;
c4 Number := -2.54973253934373;
c5 Number := 4.37466414146497;
c6 Number := 2.93816398269878;
d1 Number := 7.78469570904146E-03;
d2 Number := 0.32246712907004;
d3 Number := 2.445134137143;
d4 Number := 3.75440866190742;
plow Number := 0.02425;
phigh Number := 0.97575;
q number;
r number;

BEGIN
BEGIN
If ip_Probability < plow
then
begin
q := Sqrt(-2 * ln(ip_Probability));
result := (((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) / ((((d1 * q + d2) * q + d3) * q + d4) * q + 1);
end;
else
begin

            IF ip_Probability < phigh
            then
                begin
                    q := ip_Probability - 0.5;
                    r := q * q;
                    result := (((((a1 * r + a2) * r + a3) * r + a4) * r + a5) * r + a6) * q / (((((b1 * r + b2) * r + b3) * r + b4) * r + b5) * r + 1);
                end;
            else
                begin
                    q := Sqrt(-2 * ln(1 - ip_Probability));
                    result := -(((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) / ((((d1 * q + d2) * q + d3) * q + d4) * q + 1);
                end;
            end if;
        end;
    END IF;
end;   
RETURN result;

END;

#2

hi you posted earlier !!

what kind of help you need ???
:slight_smile: :slight_smile:

image
image.png2117×1036 159 KB

#3

Hi.

Thanks for being in touch.

I work closely with a team who run numbers on an Oracle database (Toad). They are not very collaborative tbh.
The above code is their NORMDIST and NORM INV code. When I run the code gleaned from my previous group my output is different. So I though I might post the oracle code to see if I can get it converted to MS SQL.

Are you able to help?

#4

i tried to convert the first one
please see if it works ( test it ) ..
then i will try to convert the second one

please click arrow to the left for function 
CREATE FUNCTION atom.Getnormsdist(@ip_DistributionAt FLOAT(10)) 
returns FLOAT(10)
AS 
  BEGIN 
      DECLARE @b1     FLOAT(10) = 0.319381530, 
              @b2     FLOAT(10) = -0.356563782, 
              @b3     FLOAT(10)= 1.781477937, 
              @b4     FLOAT(10) = -1.821255978, 
              @b5     FLOAT(10) = 1.330274429, 
              @p      FLOAT(10) = 0.2316419, 
              @c      FLOAT(10) = 0.39894228, 
              @t      FLOAT(10), 
              @x      FLOAT(10) = -10.1, 
              @result FLOAT(10) 

      IF @ip_DistributionAt >= 0.0 
        BEGIN 
            SET @t = 1.0 / ( 1.0 + @p * @ip_DistributionAt ); 
            SET @result = ( 1.0 - @c * Exp(-@ip_DistributionAt * @ip_DistributionAt / 2.0) * @t * ( @t * ( @t * ( @t * ( @t * @b5 + @b4 )+ @b3 ) + @b2 ) + @b1 ) ); 
        END 
      ELSE 
        BEGIN 
            SET @t = 1.0 / ( 1.0 - @p * @ip_DistributionAt ); 
            SET @result = ( @c * Exp(-@ip_DistributionAt * @ip_DistributionAt / 2.0) * @t * ( @t * ( @t * ( @t * ( @t * @b5 + @b4 ) + @b3 ) + @b2 ) + @b1 ) ); 
        END 

      RETURN @result; 
  END 

go
#5

Thanks, yes it does.

I require the NORMINV code to see if the output is comparable to Oracle output.

Really appreciate your help.

#6

Are you able to help me with the NORMINV conversion when you have a gap?

Wish I could buy you a beer!

#7

hi i tried

its giving error
Msg 3623, Level 16, State 1, Line 1
An invalid floating point operation occurred.

Error is at these statements
set @q = Sqrt(-2 * log( @ip_Probability) ));
set @q = Sqrt(-2 * log(1 - @ip_Probability) );

Other people seeing this .. will help .. lots of experts on this forum

please click arrow to the left .. function giving error 
CREATE FUNCTION GetNORMSINV(@ip_Probability FLOAT(20))
RETURNs FLOAT(20) AS
BEGIN
declare
@result FLOAT(20),
@a1 FLOAT(20) = -39.6968302866538,
@a2 FLOAT(20) = 220.946098424521,
@a3 FLOAT(20) = -275.928510446969,
@a4 FLOAT(20) = 138.357751867269,
@a5 FLOAT(20) = -30.6647980661472,
@a6 FLOAT(20) = 2.50662827745924,
@b1 FLOAT(20) = -54.4760987982241,
@b2 FLOAT(20) = 161.585836858041,
@b3 FLOAT(20) = -155.698979859887,
@b4 FLOAT(20) = 66.8013118877197,
@b5 FLOAT(20) = -13.2806815528857,
@c1 FLOAT(20) = -7.78489400243029E-03,
@c2 FLOAT(20) = -0.322396458041136,
@c3 FLOAT(20) = -2.40075827716184,
@c4 FLOAT(20) = -2.54973253934373,
@c5 FLOAT(20) = 4.37466414146497,
@c6 FLOAT(20) = 2.93816398269878,
@d1 FLOAT(20) = 7.78469570904146E-03,
@d2 FLOAT(20) = 0.32246712907004,
@d3 FLOAT(20) = 2.445134137143,
@d4 FLOAT(20) = 3.75440866190742,
@plow FLOAT(20) = 0.02425,
@phigh FLOAT(20) = 0.97575,
@q FLOAT(20),
@r FLOAT(20)

If @ip_Probability < @plow
begin
set @q = Sqrt(cast(-2 * log( @ip_Probability) as int));
set @result = (((((@c1 * @q + @c2) * @q + @c3) * @q + @c4) * @q + @c5) * @q + @c6) / ((((@d1 * @q + @d2) * @q + @d3) * @q + @d4) * @q + 1);
end;
else
begin

            IF @ip_Probability < @phigh
                 begin
                    set @q = @ip_Probability - 0.5;
                    set @r = @q * @q;
                    set @result = (((((@a1 * @r + @a2) * @r + @a3) * @r + @a4) * @r + @a5) * @r + @a6) * @q / (((((@b1 * @r + @b2) * @r + @b3) * @r + @b4) * @r + @b5) * @r + 1);
                end
            else
                begin
                    set @q = Sqrt(cast(-2  * log(1 - @ip_Probability) as int));
                    set @result = -(((((@c1 * @q + @c2) * @q + @c3) * @q + @c4) * @q + @c5) * @q + @c6) / ((((@d1 * @q + @d2) * @q + @d3) * @q + @d4) * @q + 1);
                end;
            
        
end;   
RETURN @result;
end;
#8

Haris,

There is nothing wrong with your UDF.
I tried your function with a few parameters, and all went well until I tried it with numbers >= 1.

here is the culprit:

@q = Sqrt(cast(-2  * log(1 - @ip_Probability) as int))

log(x) for x<= 0 is undefined.

give it a try with:

SELECT dbo.GetNORMSINV(0.1)	
SELECT dbo.GetNORMSINV(0.2)	

SELECT dbo.GetNORMSINV(0.9)	
SELECT dbo.GetNORMSINV(0.999)
SELECT dbo.GetNORMSINV(1.0)	--An invalid floating point operation occurred.
SELECT dbo.GetNORMSINV(1.1)	--An invalid floating point operation occurred.
#9

Hi

In my UDF

I made this changes in 2 places
Cast as int

These need to be changed ..back to original

set @q = Sqrt(cast(-2 * log( @ip_Probability) as int));
Back to
set @q = Sqrt(-2 * log( @ip_Probability))