oracle 此处不允许序列

一个插入语句，这样写报错：此处不允许序列

insert into t_dict_product_mapping(f_dict_id, f_product_code_jifei, f_product_name_jifei)

select s_dict_product_mapping.nextval dictid,gid,gname from t_dw_fltelecomproduct_all a

group by gid,gname

 

改写成这样既可继续

insert into t_dict_product_mapping(f_dict_id, f_product_code_jifei, f_product_name_jifei)

select s_dict_product_mapping.nextval dictid,gid,gname from (

select gid,gname from t_dw_fltelecomproduct_all a

group by gid,gname

)

 

报错原因，group by语句中不能是会用序列，详见帮助文档

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