Use sequence number with insert select

Question

I'm trying to execute the following statement:

INSERT INTO mySchema.ODI_PRICELIST_THREAD_TABLE
(
     src_table,
     thread_id,
     creation_date
)
    SELECT DISTINCT
           source_table AS src_table,
           num_thread_seq.nextval AS THREAD_ID,
           create_date AS CREATION_DATE
      FROM mySchema.nb_pricelist_ctrl

I need the THREAD_ID field to be a number from 1 to X where X is defined in runtime therefore I've used a sequence from 1 to X (I'm using ODI). However, I keep having the ORA-02287 Sequence not allowed error...

I've read this question and I still can't figure how I can fix my problem. I've been seaching but I'm having no luck with finding a solution. Please help

score 1 · Answer 1 · edited Apr 27 '15 at 12:18

1

From OraFaq :

The following are the cases where you can't use a sequence:

For a SELECT Statement:

In a WHERE clause

In a GROUP BY or ORDER BY clause

In a DISTINCT clause

Along with a UNION or INTERSECT or MINUS

In a sub-query

http://www.orafaq.com/wiki/ORA-02287

edited Apr 27 '15 at 12:18

DhruvJoshi

17,041
6
41
60

answered Apr 27 '15 at 12:14

ryrysz

907
5
11

Sanders the Softwarer · Accepted Answer · 2015-04-27T13:29:15.073

1

Keyword distinct is incompatible with sequence querying. If you really need it, try something like

INSERT INTO mySchema.ODI_PRICELIST_THREAD_TABLE (
 src_table,
 thread_id,
 creation_date)
select 
  a.src_table,
  num_thread_seq.nextval,
  a.create_date
from
  (select distinct src_table, create_date from mySchema.nb_pricelist_ctrl) a

edited Apr 27 '15 at 13:29

answered Apr 27 '15 at 12:15

Sanders the Softwarer

2,478
1
13
28

1

It isn't important. I'll add insert for disambiguity. – Sanders the Softwarer Apr 27 '15 at 13:28

score 0 · Answer 3 · edited Apr 27 '15 at 12:22

0

Try this

INSERT INTO mySchema.ODI_PRICELIST_THREAD_TABLE
(
     src_table,
     thread_id,
     creation_date
)
    SELECT DISTINCT
           source_table AS src_table,
           num_thread_seq.nextval() AS THREAD_ID,
           create_date AS CREATION_DATE
      FROM mySchema.nb_pricelist_ctrl

edited Apr 27 '15 at 12:22

Xstian

8,184
10
42
72

answered Apr 27 '15 at 12:19

Suryashis

1

Use sequence number with insert select

3 Answers3