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

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

insert into received messages id  content  status      values  RECEIVED MESSAGES SEQ NEXT VAL  empty blob

User · Answer

Outter joins don t work  as expected  in that case because you have explicitly told Oracle you only want data if that criteria on that table matches  In that scenario  the outter join is rendered useless   A work-around  INSERT INTO account type standard     account type Standard id  tax status id  recipient id   VALUES      SELECT account type standard seq nextval FROM DUAL      SELECT tax status id FROM tax status WHERE tax status code           SELECT recipient id FROM recipient WHERE recipient code           Edit  If you expect multiple rows from a sub-select  you can add ROWNUM 1 to each where clause OR use an aggregate such as MAX or MIN   This of course may not be the best solution for all cases    Edit  Per comment       SELECT account type standard seq nextval FROM DUAL     can be just    account type standard seq nextval

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to select

Examples related to insert