SQL & PL/SQL

Hi All,

Thanks to previous assistance from Forum contributors, I've managed to create an Oracle table that mirrors the Student's T Distribution (T-Stat) used for calculating confidence intervals. The function created with forum user's help also works well. The function user enters a sample size and confidence level and the function outputs the Critical T Value. Below is a subset of the table:

DF	CONFIDENCE_LVL	CRITICAL_T_STAT
1	0.001	636.6192487
1	0.1	6.313751514
1	0.05	12.70620473
1	0.01	63.65674115
2	0.001	31.59905458
2	0.1	2.91998558
2	0.05	4.30265273
2	0.01	9.924843201
3	0.001	12.92397864
3	0.1	2.353363435
3	0.05	3.182446305
3	0.01	5.840909309
4	0.001	8.610301581
4	0.1	2.131846782
4	0.05	2.776445105
4	0.01	4.604094871
5	0.001	6.868826626
5	0.1	2.015048372
5	0.05	2.570581835
5	0.01	4.032142983

The maximum degrees of freedom in the table is 500,000. T Tables can go to infinity but we left the degrees of freedom maximum at 500,000 and I've tried to modify the function so that if the degrees of freedom is greater than 500,000 given a user specified confidence interval, the Critical T Stat defaults to a certain value. The function compiles but when I try and enter a sample size of greater than 500,001 which is calculated as N-1 degrees of freedom or 500,001 then the function just outputs Null. I think it has something to do with the fact that the degrees of freedom greater than 500,000 doesn't exist in the table and is getting confused. I'm guessing I need to use some sort of NVL function but haven't figured out how to do it. Below is the function:

CREATE OR REPLACE FUNCTION critical_t_value
(   in_sample_size     IN   NUMBER
,   in_confidence_lvl  IN   NUMBER
)
RETURN NUMBER
IS
    return_val  NUMBER;
BEGIN
    SELECT  case when in_sample_size > 500001   and in_confidence_lvl = 0.1     then 1.6450000000 
                  when  in_sample_size > 500001 and in_confidence_lvl = 0.05   then 1.9600000000
                  when  in_sample_size > 500001 and in_confidence_lvl = 0.01   then 2.5760000000
                   when  in_sample_size > 500001 and in_confidence_lvl = 0.001 then 3.2910000000
                    else critical_t_stat end
    INTO    return_val
    FROM    students_t_table
    WHERE   df  = in_sample_size - 1
    AND        confidence_lvl   = in_confidence_lvl;
 
    RETURN  return_val;
END  critical_t_value;
/

The case statements with the default values don't work so again I'm thinking it's most likely because df > 500,000 doesn't exist. Any help would be appreciated. I'm using Oracle 10g by the way.

Thanks

Edited by: spalato76 on Mar 9, 2013 9:24 AM