SQLTeam.com | Weblogs | Forums

Convert to MS SQL

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;

hi you posted earlier !!

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

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?

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

Thanks, yes it does.

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

Really appreciate your help.

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

Wish I could buy you a beer!

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;

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.

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