PROC预编译时加入 COMMON_PARSER=YES 选项,支持预编译分析函数(比如 ROW_NUMBER 和 OVER)
示例:
SQL> DESC T1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(10)
DATE1 DATE
SQL> SELECT * FROM T1;
ID NAME DATE1
---------- ------------------------------ ------------------
101 aaa 09-SEP-13
101 bbb 10-SEP-13
101 ccc 11-SEP-13
102 ddd 08-SEP-13
102 eee 11-SEP-13
下面以 SELECT ID,NAME,DATE1 FROM (SELECT ID,NAME,DATE1,ROW_NUMBER() OVER(partition by ID order by DATE1 desc) as rn FROM T1)T WHERE T.rn=1; 语句为例,在sqlplus提示符下执行结果正常,但在proc程序里预编译就不通过。
在sqlplus提示符下执行:
SQL> SELECT ID,NAME,DATE1 FROM (SELECT ID,NAME,DATE1,ROW_NUMBER() OVER(partition by ID order by DATE1 desc) as rn FROM T1)T WHERE T.rn=1;
ID NAME DATE1
---------- ------------------------------ ------------------
101 ccc 11-SEP-13
102 eee 11-SEP-13
在proc程序里预编译出错:
Syntax error at line 7541, column 77, file test.pc:
Error at line 7541, column 77 in file test.pc
EXEC SQL SELECT ID,NAME,DATE1 FROM (SELECT ID,NAME,DATE1,ROW_NUM
BER() OVER(partition by ID order by DATE1 desc) as rn FROM T1)T WHERE T.rn=1;
............................................................................1
PCC-S-02201, Encountered the symbol "(" when expecting one of the following:
, into, from,
Error at line 0, column 0 in file test.pc
解决办法:在预编译命令行上加 COMMON_PARSER=YES 选项即可。