SQL Populating with distinct data and a sequence

Question

My problem is as follows:

• I need to populate a 'cars' table based on information from instances of rentals of the cars.
• I need to create a primary key 'car_id' but only for distinct registration plates in the rentals table.
• I am creating the car_id with a sequence.

I have tried the following code but receive an error:

--INSERT INTO cars c (c.plate, c.car_id)
SELECT DISTINCT cr.plate, car_id_seq.nextval
FROM cars_rentals cr
;

Although this will work (without distinct registration plates):

--INSERT INTO cars c (c.plate, c.car_id)
SELECT cr.plate, car_id_seq.nextval
FROM cars_rentals cr
;

(The top line is commented so I can see the values I'm trying to output straight away)

So! Does anyone know how I can either; A) Get the above code to work with DISTINCT or B) find a way to get MAXVALUE of the sequence as the DISTINCT COUNT of the registration plates (so I can do two insert statements)

Thanks in advance! Jack

Answer 1

The error is:

ORA-02287: sequence number not allowed here

This will resolve it:

SELECT cr.plate, car_id_seq.nextval
FROM (SELECT DISTINCT plate FROM cars_rentals) cr