关闭

oracle-序列 ora-02287 此处不允许序号

标签: oraclesequence
2093人阅读 评论(0) 收藏 举报
分类:

今天在生产数据库执行时报ora-02287 此处不允许序号
类似:
SELECT distinct
SYS_GUID(),
‘Y’ ACTIVE,
‘N’ IsRedBack,
‘N’ IsInit,
1 VersionNo,
‘YS2’ || LPAD(STL.SQ_YS2.NEXTVAL, 9, ‘0’) ReceivableNo
from dual
经查使用序列是有限制的:
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
所以我这里是犯了A SELECT statement with the DISTINCT operator,
修改为:
select SYS_GUID(),
‘YS2’ || LPAD(STL.SQ_YS2.NEXTVAL, 9, ‘0’) ReceivableNo,
T.*
FROM (
SELECT distinct
‘Y’ ACTIVE,
‘N’ IsRedBack,
‘N’ IsInit,
1 VersionNo
from dual
)T
这样就可以避免 A query of a view or of a materialized view

A SELECT statement with the DISTINCT operator

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:125182次
    • 积分:1459
    • 等级:
    • 排名:千里之外
    • 原创:60篇
    • 转载:23篇
    • 译文:3篇
    • 评论:5条
    文章分类
    最新评论