Oracle Version = Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
Hi Gurus
I'm trying to get sequence by using with clause and union all but I'm getting error. Please advice. Thanks in advance
Create Sequence
CREATE SEQUENCE customers_seq
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;
Query
with test1 as
(select 'abc' id1,'100' grp,customers_seq.NEXTVAL from dual
)
select id1,grp,customers_seq.nextval from test1
union all
select * from
(
with test2 as
(select 'def' id1 ,'100' grp2,customers_seq.NEXTVAL from dual)
select id1,grp2,customers_seq from test2
)
;
Error
RA-02287: sequence number not allowed here
02287. 00000 - "sequence number not allowed here"
Please guide