So I am running the following script using a subquery to return multiple records from the MTL_SYSTEM_ITEMS table and insert them into the TAXPRODCONV, but I get the ora-01427.
ORA-01427: single-row subquery returns more than one row
INSERT INTO TAXPRODCONV
(merchantid,
busnlocn,
userprodcode1,
userprodcode2,
twiprodcode)
VALUES
('01',
'DEFAULT',
(select segment1
from mtl_system_items
where segment1 like 'SV%-P%'
MINUS
select userprodcode1
from taxprodconv),
( select segment1
from mtl_system_items
where segment1 like 'SV%-P%'
MINUS
select userprodcode1
from taxprodconv),
'90000');
Note the merchantid, busnlocn and twiprodcode are constants, only the userprodcode1 and 2 will change from the subqueries I am running. I understand the ora error, because oracle thinks I am trying to insert one row. Is there a way to do this as an standard insert statement, or am I looking at writing a cursor to accomplish this task?