PL/SQL Oracle error when writing an insert statement with subquery

Question

Here is my INSERT statement:

INSERT INTO customer_payment (payment_type_id, PAYMENT_METHOD, PAYMENT_STATUS, sql_sequence)
((SELECT emcpm.payment_method_type_id,
         epmt.description, ecba.mandate_status 
  FROM cust_pay_map emcpm, payment_method_type epmt, customer_bank_account ecba 
  WHERE emcpm.payment_method_type_id = ecba.payment_method_type_id), MY_SEQ.nextval);

I get the error

ORA-00907: missing right parenthesis

when I run it. Please help me correct the mistake.

Answer 1

Add the sequence in the select statement like this:

    INSERT
INTO customer_payment
  (
    payment_type_id,
    PAYMENT_METHOD,
    PAYMENT_STATUS,
    sql_sequence
  )
SELECT emcpm.payment_method_type_id,
  epmt.description,
  ecba.mandate_status ,
  MY_SEQ.nextval
FROM cust_pay_map emcpm,
  payment_method_type epmt,
  customer_bank_account ecba
WHERE emcpm.payment_method_type_id = ecba.payment_method_type_id;