CODEWITHSUNDEEP
sqloracle-database
Ice
Ice

Reputation: 1971

Insert from select with sequence and group by

I want to insert some data into table tableA, but I have to use group by and my_sequence.nextval. It is impossible to use both in the same statment, is exists any workaround ?

For example:

insert into tableA (
  taba_id,
  taba_sum,
  taba_date
) select 
  tabb_sequence.nextval,
  sum(tabb_value),
  tabb_date
  from 
  tableB group by (tabb_date);

After execute this statment, I got: 

ORA-02287: sequence number not allowed here

According oracle documentation,I should get this error. How to deal with sequence and group by caluses in one statment?

Upvotes: 1

Views: 1689

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23371

The problem here is because your sequence is not aggregated, therefore you have this error. Try this way:

insert into tableA (
  taba_id,
  taba_date,
  taba_sum
) 
 select tabb_sequence.nextval,
        tabb_date,
        stv
   from (select tabb_date,
                sum(tabb_value) stv,
           from tableB 
          group by tabb_date) a;

Upvotes: 4

Related Questions