| Use of statistical functions in PL/SQL Script [message #37136] |
Sun, 20 January 2002 19:49  |
Steve Latz
Messages: 1
Registered: January 2002
|
Junior Member |
|
|
I am somewhat new to PL/SQL programming. I am working in V 8.1.7 trying to use CORR and COVAR_POP statistical functions on a pair of timeseries in a script. I have tested the SQL query on a PL/SQL command line with no problem [[e.g. "select corr(a.logreturn,b.logreturn) from pricehistory a, pricehistory b where a.id = 'IBM' and b.id = '.SPX' and a.valuedate = b.valuedate and a.logreturn is not null and b.logreturn is not null]].
When I try to use an analogous query in a script, I get a compile error that "CORR" is not declared.
Does one have to do something special to make the Oracle SQL statistical functions "visible" in a PL/SQL script?
Any help or insight would be appreciated.
Thanks
|
|
|
|
| Re: Use of statistical functions in PL/SQL Script [message #37140 is a reply to message #37136] |
Mon, 21 January 2002 00:48  |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
Hi,
i think it is bug in the pl/sql parser.in oracle 8.x... SQL engine and pl/sql engine use different parsers.PL/SQL engine use the same parser as SQL in Oracle9i.
so workaround is to use dynamic pl/sql(which uses sql engine).
here is an example;
SQL> select * from my_table;
COL1 COL2
--------- ---------
1 2
1 2
3 4
34 44
SQL> set serveroutput on
SQL> declare
2 n number;
3 begin
4 execute immediate 'select corr(col1,col2) from my_table' into n;
5 dbms_output.put_line (n);
6 end;
7 /
.9999198136110651179564758875262072741699
PL/SQL procedure successfully completed.
Hope it helps!!!
cheers
pratap
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
