ORA-02287: 此处不允许序号

dsgzxy527

转自：http://blog.sina.com.cn/s/blog_4ea0bbed0100s7wp.html  作者：nowhill

开发人员反映序列不能使用，自己回来测试了一下

select test.sequence.nextval,mgr,sum(sal) from emp group by mgr

ORA-02287: 此处不允许序号

果然，在外面再包一层就可以了
SQL> select test_sequence.nextval,mgr,sm from (select mgr,sum(sal) sm from emp group by mgr );

   NEXTVAL   MGR         SM
---------- ----- ----------
        27  7839       8275
        28  7782       1300
        29  7698       6550
        30  7902       5800
        31  7566       6000
        32  7788       1100

6 rows selected

看来序列使用时有限制的
看到书上说有这么多限制
Restrictions on Sequence Values You cannot use CURRVAL and NEXTVAL in the
following constructs:
■ A subquery in a DELETE, SELECT, or UPDATE statement
■ A query of a view or of a materialized view
■ A SELECT statement with the DISTINCT operator
■ A SELECT statement with a GROUP BY clause or ORDER BY clause    --这个就是我遇到的那种情况
■ A SELECT statement that is combined with another SELECT statement with the
UNION, INTERSECT, or MINUS set operator
■ The WHERE clause of a SELECT statement
■ The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
■ The condition of a CHECK constrain