Oracle Insert via Select from multiple tables where one table may not have a row
- by Mikezx6r
I have a number of code value tables that contain a code and a description with a Long id.
I now want to create an entry for an Account Type that references a number of codes, so I have something like this:
insert into account_type_standard (account_type_Standard_id,
tax_status_id, recipient_id)
( select account_type_standard_seq.nextval,
ts.tax_status_id, r.recipient_id
from tax_status ts, recipient r
where ts.tax_status_code = ?
and r.recipient_code = ?)
This retrieves the appropriate values from the tax_status and recipient tables if a match is found for their respective codes. Unfortunately, recipient_code is nullable, and therefore the ? substitution value could be null. Of course, the implicit join doesn't return a row, so a row doesn't get inserted into my table.
I've tried using NVL on the ? and on the r.recipient_id.
I've tried to force an outer join on the r.recipient_code = ? by adding (+), but it's not an explicit join, so Oracle still didn't add another row.
Anyone know of a way of doing this?
I can obviously modify the statement so that I do the lookup of the recipient_id externally, and have a ? instead of r.recipient_id, and don't select from the recipient table at all, but I'd prefer to do all this in 1 SQL statement.
