1. Registered User
Join Date
Jul 2012
Posts
4

Hi ,

Please help me in finding equivalent function in DB2 that is used as NORMSDIST function in excel.

or if there is no function in DB2 can you please guide if there are any methods that we can use to derrive NORMSDIST function.

thnanks in anticipation,
Praneet

2. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
What are the specifications of the NORMSDIST function?
- Input parameters(mandatory and optional) and their datatypes.
- Return value and its datatype

3. Registered User
Join Date
Jul 2012
Posts
4
The following function is used in excel and we need to implement this in DB2
MIN(2*NORMSDIST(H2),2*(1-NORMSDIST(H2))

where H2 is cell value

H2 Values are like 1.3387
and the value return for the above function is 0.18066

Yes Values are Mandatory and their data type is Decimal Values
and return value is also decimal .

thanks,
Praneet

4. Registered User
Join Date
Jan 2007
Location
Jena, Germany
Posts
2,721
What does NORMSDIST do? Is it this one: NORMSDIST function ? If so, you have the formula that you could implement with the corresponding SQL operations, couldn't you?

5. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Thank you, Stolze and Praneet.

Looking in reference, Normal Distribution Function can NOT be calculaed only by elementary funcions.
Integral calculus is necessary to calculate exactly.
So, some approximations must be necessary on SQL.

Here is a trial example.
But, evaluation of accuracy is insufficient...
- Selection of approximation functions
- Boundary of each functions
- Margin of error
- Precision of parameter and return value
- so on...

Example 1:
Code:
```------------------------------ Commands Entered ------------------------------
CREATE OR REPLACE FUNCTION normsdist(z DEC(9 , 8) )
RETURNS DEC(9 , 8)
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
/*
Referenced materials:
(1) Normal Distribution Function
http://mathworld.wolfram.com/NormalDistributionFunction.html

(2) Divisors for (10) in (1)
Sloane's A014481
http://oeis.org/A014481

(3) Maclaurin series for erf
http://mathworld.wolfram.com/Erf.html
*/
RETURN
0.5
+ CASE
WHEN ABS(z) < 2 THEN
/* derived from Maclaurin series for erf -- (10) in (1) */
(        z
- POWER(z ,  3) /             6
+ POWER(z ,  5) /            40
- POWER(z ,  7) /           336
+ POWER(z ,  9) /          3456
- POWER(z , 11) /         42240
+ POWER(z , 13) /        599040
- POWER(z , 15) /       9676800
+ POWER(z , 17) /     175472640
- POWER(z , 19) /    3530096640
+ POWER(z , 21) /   78033715200
- POWER(z , 23) / 1880240947200 /* Sloane's A014481 */
)  / SQRT(2. * 3.14159265358979)
ELSE
/* Approximation due to Bagby (1995) -- (14) in (1) */
SIGN(z)
* SQRT(
1 - (   7 * EXP( - z*z / 2 )
+ 16 * EXP( - z*z * (2 - SQRT(2)) )
+ (7 + 3.14159265358979 * z*z / 4) * EXP( - z*z )
) / 30
) / 2
END
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.```

Use of the example 1:
Code:
```------------------------------ Commands Entered ------------------------------
VALUES MIN( 2 * NORMSDIST(1.3387) , 2 * (1 - NORMSDIST(1.3387) ) );
------------------------------------------------------------------------------

1
---------------------------------
0.18066836

1 record(s) selected.```

6. Registered User
Join Date
Jul 2012
Posts
4
Thank you Very Much tonkuma..The below routine is working for us.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•