Oracle SQL: Use sequence in insert with Select Statement

Question

Basically I want to run the following query:

INSERT INTO historical_car_stats (historical_car_stats_id, year, month, make, model, region, avg_msrp, count)
  SELECT
    my_seq.nextval,
    '2010',
    '12',
    'ALL',
    'ALL',
    region,
    sum(avg_msrp * count) / sum(count),
    sum(count)
  FROM historical_car_stats
  WHERE year = '2010'
        AND month = '12'
        AND make != 'ALL'
  GROUP BY region;

It doesn't work because "sequence number not allowed here" SQL error. How can I write this so Oracle will let me do what I want to do?

Justin Cave · Accepted Answer · 2011-08-19 18:56:07Z

84

Assuming that you want to group the data before you generate the key with the sequence, it sounds like you want something like

INSERT INTO HISTORICAL_CAR_STATS (
    HISTORICAL_CAR_STATS_ID, 
    YEAR,
    MONTH, 
    MAKE,
    MODEL,
    REGION,
    AVG_MSRP,
    CNT) 
SELECT MY_SEQ.nextval,
       year,
       month,
       make,
       model,
       region,
       avg_msrp,
       cnt
  FROM (SELECT '2010' year,
               '12' month,
               'ALL' make,
               'ALL' model,
               REGION,
               sum(AVG_MSRP*COUNT)/sum(COUNT) avg_msrp,
               sum(cnt) cnt
          FROM HISTORICAL_CAR_STATS
         WHERE YEAR = '2010' 
           AND MONTH = '12'
           AND MAKE != 'ALL' 
         GROUP BY REGION)

answered Aug 19, 2011 at 18:56

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

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

4 Comments

Somnath Musib Over a year ago

What if sequence column needs to appear in the middle of the table.

Dariusz Over a year ago

It seems that the rule is that there can be no "where" in the outermost select.

Jay Kumar R Over a year ago

@SomnathMusib you are a genius. TCS right.. you will be.

Somnath Musib Over a year ago

@JayKumarR Thanks for the sarcasm. I had faced this issue. While writing data in a flat file (for mainframe system), I had to get data from a table and add the line no (a sequence) in middle of the record.

Michael · Accepted Answer · 2018-04-11 16:50:50Z

11

I tested and the script run ok!

INSERT INTO HISTORICAL_CAR_STATS (HISTORICAL_CAR_STATS_ID, YEAR,MONTH,MAKE,MODEL,REGION,AVG_MSRP,COUNT) 
WITH DATA AS
(
    SELECT '2010' YEAR,'12' MONTH ,'ALL' MAKE,'ALL' MODEL,REGION,sum(AVG_MSRP*COUNT)/sum(COUNT) AVG_MSRP,sum(Count) COUNT
    FROM HISTORICAL_CAR_STATS
    WHERE YEAR = '2010' AND MONTH = '12'
    AND MAKE != 'ALL' GROUP BY REGION
)
SELECT MY_SEQ.NEXTVAL, YEAR,MONTH,MAKE,MODEL,REGION,AVG_MSRP,COUNT
FROM DATA;

you can read this article to understand more! http://www.orafaq.com/wiki/ORA-02287

edited Apr 11, 2018 at 16:50

Michael

7356 silver badges14 bronze badges

answered Oct 14, 2013 at 1:23

Trieu Tuan Nghia

1111 silver badge4 bronze badges

1 Comment

Fred Over a year ago

It works. However you have a small error. Where it reads 'WITH DATA IS' it should be 'WITH DATA AS'.

Collectives™ on Stack Overflow

Oracle SQL: Use sequence in insert with Select Statement

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related