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

Sanders the Softwarer · Accepted Answer · 2015-04-27 13:29:15Z

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, 2015 at 13:29

answered Apr 27, 2015 at 12:15

Sanders the Softwarer

2,5061 gold badge15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sanders the Softwarer Over a year ago

It isn't important. I'll add insert for disambiguity.

DhruvJoshi · Accepted Answer · 2015-04-27 12:18:28Z

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, 2015 at 12:18

DhruvJoshi

17.2k6 gold badges46 silver badges63 bronze badges

answered Apr 27, 2015 at 12:14

ryrysz

9075 silver badges11 bronze badges

Comments

Xstian · Accepted Answer · 2015-04-27 12:22:27Z

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, 2015 at 12:22

Xstian

8,29110 gold badges47 silver badges76 bronze badges

answered Apr 27, 2015 at 12:19

Suryashis

1

Collectives™ on Stack Overflow

Use sequence number with insert select

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related