Database Software

Hi

I'd like to be able to persuade OWB to build code that does a MERGE statement without an UPDATE part, i.e. the WHEN MATCHED bit of code - like in this test script below...

drop table test_target;
drop table test_source;
create table test_target(col1 number,col2 varchar2(20));
alter table test_target add constraint tt_pk primary key (col1);
insert into test_target values(1,'Test1');
commit;
create table test_source(col1 number,col2 varchar2(20));
alter table test_source add constraint ts_pk primary key (col1);
insert into test_source values(2,'Test2');
commit;
merge into test_target tt
using test_source ts
on (ts.col1 = tt.col1)
when not matched then
insert (col1,col2)
values(ts.col1,ts.col2)
/
select * from test_target;
1 Test1
2 Test2

I've set up a mapping to use INSERT/UPDATE (also tried UPDATE/INSERT) without constraints and set the Match/Load properties invidually at column level. The mapping won't validate - gives error:

VLD-2762: Update statement cannot be properly generated.

This also means it won't deploy when I try. The code itself looks fine though...so I saved it and tried to install it manually - it gave an error due to a missing variable declaration (why ??) which I fixed and then it compiled fine.

I realise I could write a MINUS Operation to determine the difference between the source and targets and just use an INSERT mapping but I wanted to do it the MERGE route. (I'll do this if necessary)

Anybody have any ideas as to why OWB can't generate such a MERGE statement ?

Thanks
Jeff