Is it possible to get a result if I combine the WITH clause and MERGE statement directly into one SELECT statement? Combining WITH clause with INSERT, UPDATE and DELETE works but MERGE does not work with “WITH clause” in DB2.

Here are fiddle link for INSERT, UPDATE and DELETE.
and fiddle link for MERGE

db2 code:

CREATE TABLE target_table (id, name, value) AS 
(
VALUES 
  (1, 'item1', 1)
, (2, 'item1', 1)
, (3, 'item1', 1)
, (4, 'item2', 1)
, (5, 'item3', 1)
, (6, 'item3', 1)
, (7, 'item3', 9)
, (8, 'item3', 9)
, (9, 'item3', 9)
) WITH DATA

CREATE TABLE source_table (id, name, value) AS 
(
VALUES 
  (1, 'item1', 2)
) WITH DATA


with 
    cte_1_insert as (
    SELECT count (1) as DUMMY_1 from NEW TABLE
    (
        MERGE INTO target_table AS tgt
        USING source_table AS src
        ON tgt.id = src.id
        WHEN MATCHED THEN
            UPDATE SET tgt.name = src.name, tgt.value = src.value
        WHEN NOT MATCHED THEN
            INSERT (id, name, value) VALUES (src.id, src.name, src.value)
    ))
  
select * from target_table order by ID ASC;

expected result:

------------------------------------|
|ID     |NAME       |VALUE  |
|1      |item1      |2      |
|2      |item1      |1      |
|3      |item1      |1      |
|4      |item2      |1      |
|5      |item3      |1      |
|6      |item3      |1      |
|7      |item3      |9      |
|8      |item3      |9      |
|9      |item3      |9      |
------------------------------------|