CODEWITHSUNDEEP
sqloracle-databaseoracle11g
srtmonkey
srtmonkey

Reputation: 85

Inserting a sequence with a distinct

I know it is not possible to insert a sequence with a SELECT DISTINCT and you have to somehow nest the distinct in the FROM I just cannot see it. Here is the insert statement

INSERT INTO rental (rental_id, customer_id, check_out_date, return_date, created_by, creation_date, last_updated_by, last_update_date)
SELECT DISTINCT  rental_s1.nextval, c.contact_id, tu.check_out_date, tu.return_date, m.created_by, m.creation_date, m.last_updated_by, m.last_update_date
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN transaction_upload tu
ON       c.first_name = tu.first_name
AND      NVL(c.middle_name,'x') = NVL(tu.middle_name,'x')
AND      c.last_name = tu.last_name  
AND      m.account_number = tu.account_number LEFT JOIN rental r
ON       tu.check_out_date = r.check_out_date;

As of right not it is giving me an ORA-02287:Sequence number not allowed here How might I change this to allow all other values distinct and still be able to add the sequence.

Upvotes: 1

Views: 1056

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23371

This should solve your problem:

INSERT INTO rental (rental_id, customer_id, check_out_date, 
                    return_date, created_by, creation_date, 
                    last_updated_by, last_update_date)
SELECT rental_s1.nextval, contact_id, check_out_date, return_date,
       created_by, creation_date, last_updated_by, last_update_date
  FROM ( SELECT DISTINCT c.contact_id, tu.check_out_date, 
                         tu.return_date, m.created_by, 
                         m.creation_date, m.last_updated_by, 
                         m.last_update_date
           FROM member m 
                 INNER JOIN contact c ON m.member_id = c.member_id 
                 INNER JOIN transaction_upload tu ON c.first_name = tu.first_name
                        AND NVL(c.middle_name,'x') = NVL(tu.middle_name,'x')
                        AND c.last_name = tu.last_name  
                        AND m.account_number = tu.account_number 
                  LEFT JOIN rental r ON tu.check_out_date = r.check_out_date
       ) tab

Upvotes: 2

Related Questions