CODEWITHSUNDEEP
oracle-databasesequence
SaintLike
SaintLike

Reputation: 9569

Use sequence number with insert select

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

Upvotes: 0

Views: 2297

Answers (3)

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

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

Upvotes: 1

Suryashis
Suryashis

Reputation: 1

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

Upvotes: 0

ryrysz
ryrysz

Reputation: 907

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

Upvotes: 1

Related Questions