I am struggling to get this function to run correctly and return the value of the F-test. The online documentation indicates that the proper syntax is:
STATS_F_TEST(cust_gender, cust_credit_limit, 'STATISTIC', 'F') f_statistic
However, that syntax returns "illegal argument for function" when I attempt it in the following query:
With ClmSz as
( Select
(Case
When ClaimAmount > 4000000 then 'L'
Else 'S'
End) ClmType,
ClaimAmount
From
Tia_Oracle.Claim_Master)
Select
Stats_F_Test(ClmType, ClaimAmount,'STATISTIC','F') "FTestResult"
From
ClmSz;
The same query returns the expected results for other values of the third parameter. What am I doing wrong?
I reached out to Keith Laker, PM for analytical SQL. He had the following to say about this:
"You are attempting to compare to values from a list of values to determine if there is a significant relationship between them. It's the 3rd expression that is causing the illegal argument. The 3rd expression has to be a value of 1 or 2 depending on which of the two groups specified by expr1 is the high value or numerator.
Therefore, this works:
create table T (x int, y number );
insert into T select 1, dbms_random.value from dual connect by level<= 10;
insert into T select 2, dbms_random.value from dual connect by level<= 10;
Select Stats_F_Test(x, y) From T;
STATS_F_TEST(X,Y)
---------------------------------------
3.7E-01
SELECT STATS_F_TEST(x, y, 'STATISTIC', 2) f_statistic
FROM T;
F_STATISTIC
---------------------------------------
5.4E-01
expr3 in this example can be either 1 or 2. If you extend your example by adding another unique value then you will get an error again
create table T (x int, y number );
insert into T select 1, dbms_random.value from dual connect by level<= 10;
insert into T select 2, dbms_random.value from dual connect by level<= 10;
insert into T select 3, dbms_random.value from dual connect by level<= 10;
Select Stats_F_Test(x, y) From T;
SQL Error: ORA-01760: illegal argument for function
SELECT STATS_F_TEST(x, y, 'STATISTIC', 2) f_statistic
FROM T;
SQL Error: ORA-01760: illegal argument for function
"
So the final parameter needs to be one of the values for ClmType, which looks like either L or S in your example.