Issue with query in DB2 --Not able to find the cause

Question

I was trying to execute the below query in Db2 and its giving me some error, which I am not able to identify the root cause.Could someone please help here.

with
  test as(
  select * from (
    select
      ID,SOURCE,NUMBERD
     from TABLE where RND='4')t (ID,SOURCE,NUMBERD)
     ),
  t as (
    select
      ID,
      count(*) qnt,
      count(distinct SOURCE) distinct_qnt,
      count(distinct NUMBERD) NUMBERD_CNT,
      sum(case when SOURCE = 'EXTERNAL' then 1 else 0 END) external_qnt,
      sum(case when SOURCE <> 'EXTERNAL' then 1 else 0 END) internal_qnt
    from test
    group by ID
  )

    delete from TABLE where RND='4' and ID in(
    select ID from(
    select
    ID,
    case when (external_qnt = 0 or external_qnt = qnt or NUMBERD_CNT >1) then 3
    when (external_qnt = 1 and distinct_qnt = qnt and internal_qnt=0) then 2 else 1 END scenario_id
    from t where case when (external_qnt = 0 or external_qnt = qnt or NUMBERD_CNT >1) then 3
            when (external_qnt = 1 and distinct_qnt = qnt and internal_qnt=0) then 2 else 1 END in(2,3))asd)

The error which I am getting when executing the above query is

DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=delete from;by qsMatchSetID ) ;<from>, DRIVER=3.58.81

But if I comment the delete portion alone and execute the query it is working fine, something like below.

        with
  test as(
            ...............
            ...........
                from test
        group by ID
      )
    --delete from TABLE where RND='4' and ID in(
    select ID from(
    select
    ID,
    case when (external_qnt = 0 or external_qnt = qnt or NUMBERD_CNT >1) then 3
    when (external_qnt = 1 and distinct_qnt = qnt and internal_qnt=0) then 2 else 1 END scenario_id
    from t where case when (external_qnt = 0 or external_qnt = qnt or NUMBERD_CNT >1) then 3
            when (external_qnt = 1 and distinct_qnt = qnt and internal_qnt=0) then 2 else 1 END in(2,3))
            ---asd)

Answer 1

You may use data-change-table-reference of subselect, if you want to use CTE for the data change statement.

declare global temporary table session.table (rnd varchar(10), id int, SOURCE varchar(10), NUMBERD int) 
with replace on commit preserve rows not logged;

with
  test as
(
  select * 
  from 
  (
    select ID, SOURCE, NUMBERD
    from SESSION.TABLE 
    where RND='4'
  ) t (ID,SOURCE,NUMBERD)
 )
 , t as 
 (
    select
      ID,
      count(*) qnt,
      count(distinct SOURCE) distinct_qnt,
      count(distinct NUMBERD) NUMBERD_CNT,
      sum(case when SOURCE = 'EXTERNAL' then 1 else 0 END) external_qnt,
      sum(case when SOURCE <> 'EXTERNAL' then 1 else 0 END) internal_qnt
    from test
    group by ID
)

SELECT COUNT(1)
FROM OLD TABLE 
(
  delete from SESSION.TABLE 
  where RND='4' and ID in
  (
    select ID 
    from
    (
    select
      ID
    , case 
        when (external_qnt = 0 or external_qnt = qnt or NUMBERD_CNT >1) then 3
        when (external_qnt = 1 and distinct_qnt = qnt and internal_qnt=0) then 2 else 1 
      END scenario_id
    from t 
    where 
      case 
        when (external_qnt = 0 or external_qnt = qnt or NUMBERD_CNT >1) then 3
        when (external_qnt = 1 and distinct_qnt = qnt and internal_qnt=0) then 2 
        else 1 
      END in (2,3)
    ) asd
  )
);