[sql] Oracle Insert via Select from multiple tables where one table may not have a row

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.

It was not clear to me in the question if ts.tax_status_code is a primary or alternate key or not. Same thing with recipient_code. This would be useful to know.

You can deal with the possibility of your bind variable being null using an OR as follows. You would bind the same thing to the first two bind variables.

If you are concerned about performance, you would be better to check if the values you intend to bind are null or not and then issue different SQL statement to avoid the OR.

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 = ? OR (ts.tax_status_code IS NULL and ? IS NULL))
and (r.recipient_code = ? OR (r.recipient_code IS NULL and ? IS NULL))

insert into received_messages(id, content, status)
    values (RECEIVED_MESSAGES_SEQ.NEXT_VAL, empty_blob(), '');

Try:

insert into account_type_standard (account_type_Standard_id, tax_status_id, recipient_id)
select account_type_standard_seq.nextval,
       ts.tax_status_id, 
       ( select r.recipient_id
         from recipient r
         where r.recipient_code = ?
       )
from tax_status ts
where ts.tax_status_code = ?

A slightly simplified version of Oglester's solution (the sequence doesn't require a select from DUAL:

INSERT INTO account_type_standard   
  (account_type_Standard_id, tax_status_id, recipient_id) 
VALUES(   
  account_type_standard_seq.nextval,
  (SELECT tax_status_id FROM tax_status WHERE tax_status_code = ?),
  (SELECT recipient_id FROM recipient WHERE recipient_code = ?)
)

insert into account_type_standard (account_type_Standard_id, tax_status_id, recipient_id)
select account_type_standard_seq.nextval,
   ts.tax_status_id, 
   ( select r.recipient_id
     from recipient r
     where r.recipient_code = ?
   )
from tax_status ts
where ts.tax_status_code = ?