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