merge statement - why it is failing

[annecarterfredi]

MERGE INTO MyTable as nd USING
(with tex (ID, acc_num) as ( values ('1','acc232'), ('2','acc8993'),
('3','acc543') )
select ID, acc_num from tex
) as T
on nd.id = T.ID
WHEN NOT MATCHED THEN
INSERT (ID) VALUES (T.ID) ;

Error message:
"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "as" was found following "th tex (ID,
acc_num)".
Expected tokens may include: "JOIN". SQLSTATE=42601

SQL0104N An unexpected token "as" was found following "th tex (ID,
acc_num)". Expected tokens may include:
"JOIN ".
"

What am I doing wrong..?

Thanks,

[Serge Rielau]

an*************@gmail.com wrote:

MERGE INTO MyTable as nd USING
(with tex (ID, acc_num) as ( values ('1','acc232'), ('2','acc8993'),
('3','acc543') )
select ID, acc_num from tex
) as T
on nd.id = T.ID
WHEN NOT MATCHED THEN
INSERT (ID) VALUES (T.ID) ;

Error message:
"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "as" was found following "th tex (ID,
acc_num)".
Expected tokens may include: "JOIN". SQLSTATE=42601

SQL0104N An unexpected token "as" was found following "th tex (ID,
acc_num)". Expected tokens may include:
"JOIN ".
"

What am I doing wrong..?

The WITH clause isn't supported in subqueries.
But why so complicated?
MERGE INTO MyTable as nd
USING (values ('1','acc232'), ('2','acc8993'),
('3','acc543') ) AS T(ID, acc_num)
....

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab