Combining conditional copies and literals in an INSERT INTO statement

Question

I have a table with a primary key and two foreign keys. I am trying to copy data from the source of one of the foreign keys conditionally, and use a literal for the other foreign key, somewhat like the below statement:

INSERT INTO TARGET_CROSSREF_TABLE (FOREIGN_KEY_COND, FOREIGN_KEY_LITERAL) 
VALUES ((SELECT DATA_SOURCE_TABLE.FOREIGN_KEY_COND WHERE <CONDITION>), 1);

The idea is that I want every row from DATA_SOURCE_TABLE to go into TARGET_REFERENCE_TABLE, but for FOREIGN_KEY_LITERAL (which is required to not be null), to always be set to 1 for each entry. However, when I run this statement, I get this error message:

ORA-01427: single-row subquery returns more than one row

I know the subquery is returning more than one row. That's what I designed it to do. That was the plan all along. How do I pass that info along to my SQL script so it does what I want?

Answer 1

I was able to do this by using a Select As statement.

INSERT INTO TARGET_CROSSREF_TABLE
(SELECT 0 AS PRIMARY_KEY,
DATA_SOURCE_TABLE.FOREIGN_KEY_COND AS FOREIGN_KEY_COND,
 1 AS FOREIGN_KEY_LITERAL
FROM DATA_SOURCE_TABLE WHERE >CONDITION GOES HERE<)

Note that I have a trigger for the primary key that forces it to be correct. I just needed something in there.