Team

Here is the three tables created succssfully
================================

db2 "create table emp (eid int not null primary key,salary int)"
DB20000I The SQL command completed successfully.


db2 "create table mgr (eid int not null primary key,bonus int)"
DB20000I The SQL command completed successfully.


db2 "create table src (eid int not null primary key,salary int,bonus int,ismgr char (1))"
DB20000I The SQL command completed successfully.

1st query is success
=================

db2 "with i1 as (select eid, bonus, ismgr from new table (insert into emp include (bonus int, ismgr char(1)) select eid, salary,bonus,ismgr from src)) select count(*) from new table (insert into mgr select eid,bonus from i1 where ismgr='Y')"

1
-----------
0

1 record(s) selected.

2nd Query failed <--- Please help
=============
db2 "with i1 as (select eid, salary, bonus from new table (merge into emp include (bonus int, ismgr char(1)) using src on (1=0) when not matched and ismgr='N' then insert (eid,salary) values(src.eid,src.salary) when not matched nd ismgr='Y' then set eid=src.eid,salary=src.salary,bonus=src.bonus,ismg r=src.ismgr)select
count(*) from new table (insert into mgr select eid,salary,bonus from i1 where ismgr='Y')"

SQL0104N An unexpected token "with i1 as (select eid, sala" was found
following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<labeled_begin_atomic>". SQLSTATE=42601

Thanks
Ra

What are you trying to do with the second query?

Andy

To accomplish merge statement/output

Thanks
Raj

The MERGE statement is for modifying data only. You cannot use it in a select--just like you cannot use INSERT, UPDATE, or DELETE.

Andy