If customer id exists in table A, insert order in table B. if customer id does not exist in table A, insert customer id in table A and then order in table B. I have been trying to achieve this with if/else and merge but keep running into invalid sql statement.

IF EXISTS (SELECT CustomerID FROM Customer_T WHERE CustomerID = 18)
    Insert into Order_T
    values(79,18,to_date('09/28/2021','mm/dd/yyyy'),to_date('10/01/2021','mm/dd/yyyy'),1,3)
ELSE
  insert INTO Customer_T VALUES (18,'Capitol Industries Ltd', '999 Fifth Avenue', 'New York', 'NY','10015')
  insert into Order_T values (79,18,to_date('09/28/2021','mm/dd/yyyy'),to_date('10/01/2021','mm/dd/yyyy'),1,3)
END IF;

CodePudding user response：

The IF THEN ELSE logic is not needed for this case. Instead make use of the database built-in functionality. customerid should be your primary key, so if you try to insert and it already exists that will raise the DUP_VAL_ON_INDEX exception.

Check the following example:

-- create tables
create table customers (
    id                             number generated by default on null as identity 
                                   constraint customers_id_pk primary key,
    name                           varchar2(255 char)
)
;

create table orders (
    id                             number generated by default on null as identity 
                                   constraint orders_id_pk primary key,
    customer_id                    number
                                   constraint orders_customer_id_fk
                                   references customers on delete cascade,
    product                        varchar2(100 char)
)
;

BEGIN
  BEGIN
    insert INTO customers VALUES (2,'Capitol Industries Ltd');
  EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
    NULL; 
  END;
  insert into orders (customer_id,product) values (2,'a book');
END;  
/

run the above block a couple of times. Only the first time it will insert a customer.

CodePudding user response：

Suppose you have 2 very simple tables.

Tables

create table T1( num_ )
as
select 1 from dual ;

create table T2( num_ )
as
select 200 from dual ;

An anonymous block, containing IF .. ELSE .. END IF and EXISTS() similar to the code in your question, causes an error:

function or pseudo-column 'EXISTS' may be used inside a SQL statement only

    begin
      if exists( select num_ from T1 where num_  = 2 ) then
        insert into T2( num_ ) values( 2 ) ;
        dbms_output.put_line( 'if' ) ;
      else
        insert into T1( num_ ) values( 2 ) ;
        insert into T2( num_ ) values( 2 ) ;
        dbms_output.put_line( 'else' ) ;
      end if ;
    end ;
    /
-- error:
... function or pseudo-column 'EXISTS' may be used inside a SQL statement only

One solution may be to do the following (see asktom.oracle.com - Equivalent for EXISTS() in an IF statement)

begin
  for x in ( select count(*) cnt
             from dual
             where exists ( select num_ from T1 where num_  = 2  ) 
  ) loop
    if ( x.cnt = 1 ) then                  -- found
      insert into T2( num_ ) values( 2 ) ;
      dbms_output.put_line( 'if' ) ;
    else                                   -- not found
      insert into T1( num_ ) values( 2 ) ;
      insert into T2( num_ ) values( 2 ) ;
      dbms_output.put_line( 'else' ) ; 
    end if;
  end loop;
end;
/

-- output:
1 rows affected

dbms_output:
else  

After the first execution of the anonymous block, the tables contain the following rows:

select num_, '<- T1' as table_ from T1
union all
select num_, '<- T2' from T2 ;

-- result
NUM_    TABLE_
1       <- T1
2       <- T1
200     <- T2
2       <- T2

Execute the anonymous block again, and you get ...

1 rows affected

dbms_output:
if

-- tables
NUM_    TABLE_
1       <- T1
2       <- T1
200     <- T2
2       <- T2
2       <- T2

DBfiddle here.

CodePudding user response：

You may use multitable insert and use the fact that aggregate function without group by always returns a row with null in case of absent (= not satisfying where condition) row.

The code is below:

insert into customers(id, name, company, state)
values (1, 'Some name', 'Some company', 'NY')

1 rows affected

insert all
  when cust_exists = 0
  then
    into customers (id, name, company, state)
    values (cust_id, cust_name, company, state)

  when 1 = 1
  then
    into orders (id, customer_id, order_date, due_date, some_id)
    values(order_id, cust_id, order_date, due_date, some_id)
select
  1 as order_id,
  1 as cust_id,
  'Some other name' as cust_name,
  'Company' as company,
  'NY' as state,
  date '2021-09-28' as order_date,
  date '2021-10-03' as due_date,
  100 as some_id,
  nvl(max(1), 0) as cust_exists
from customers
where id = 1

1 rows affected

insert all
  when cust_exists = 0
  then
    into customers (id, name, company, state)
    values (cust_id, cust_name, company, state)

  when 1 = 1
  then
    into orders (id, customer_id, order_date, due_date, some_id)
    values(order_id, cust_id, order_date, due_date, some_id)
select
  2 as order_id,
  2 as cust_id,
  'Some other name' as cust_name,
  'Company' as company,
  'NY' as state,
  date '2021-09-28' as order_date,
  date '2021-10-03' as due_date,
  100 as some_id,
  nvl(max(1), 0) as cust_exists
from customers
where id = 2

2 rows affected

You may also use two inserts with documented ignore_row_on_dupkey_index hint, which does what its name implies.

insert /* 
  ignore_row_on_dupkey_index(customers(id))
*/
into customers (id, name, company, state)
values (2, 'Name', 'Comp', 'NY')

✓

insert into orders (id, customer_id, order_date, due_date, some_id)
values (3, 2, date '2021-09-30', date '2021-10-07', 5)

1 rows affected

select *
from customers

ID	NAME	COMPANY	STATE
1	Some name	Some company	NY
2	Some other name	Company	NY

db<>fiddle here