Deletes in source table not propagated to target table

[Ole_Dufour]

In my CDC only task I have a table definition 'S_ADDR_PER'
In that table definition I have a transformation field called 'ROWID_CLIENT'  defined as follows : 

  source_lookup('NO_CACHING','SIEBEL','S_ADDR_PER',' AP.ROW_ID from
  SIEBEL.S_CONTACT C,
  SIEBEL.S_CON_ADDR a,
  SIEBEL.S_ADDR_PER ap
  where ap.ROW_ID = a.ADDR_PER_ID
  and C.PR_GRP_OU_ID = a.ORG_GROUP_ID
  and c.PRIV_FLG = ''N''
  and c.AGENT_FLG != ''Y''
  and c.EMP_FLG != ''Y''
  and AP.ROW_ID=''' || $ROW_ID || ''' --' ,'z')

The filter for my table is as follows, $ROW_ID being the primary key of the S_ADDR_PER table :

  $ROW_ID = $ROWID_CLIENT

See screen captures

My question is why deletions in the source table won't cause deletions in the target table. Inserts and updates work fine. Changing the filter to be :

  $ROW_ID = $ROWID_CLIENT
  or $AR_H_OPERATION ='DELETE'

... doesn't work either. Only when removing the filter entirely, deletions get propagated.

[john_wang]

Hi @Ole_Dufour ,

Would you please share the source and target endpoints database type, and Replicate version? We'd like to confirm the behavior for you.

Regards,

John.

[Ole_Dufour]

Hi John,

Our version : 2023.11.0.259
Source and Target database : Oracle

I noticed something strange.

In the transformation I replaced :
 source_lookup('NO_CACHING','SIEBEL','S_ADDR_PER',' AP.ROW_ID from
  SIEBEL.S_CONTACT C,
  SIEBEL.S_CON_ADDR a,
  SIEBEL.S_ADDR_PER ap
  where ap.ROW_ID = a.ADDR_PER_ID
  and C.PR_GRP_OU_ID = a.ORG_GROUP_ID
  and c.PRIV_FLG = ''N''
  and c.AGENT_FLG != ''Y''
  and c.EMP_FLG != ''Y''
  and AP.ROW_ID=''' || $ROW_ID || ''' --' ,'z')

... by :
  source_lookup('NO_CACHING','SIEBEL','S_ADDR_PER',' A.ADDR_PER_ID from
  SIEBEL.S_CONTACT C,
  SIEBEL.S_CON_ADDR a
  where
  C.PR_GRP_OU_ID = a.ORG_GROUP_ID
  and c.PRIV_FLG = ''N''
  and c.AGENT_FLG != ''Y''
  and c.EMP_FLG != ''Y''
  and a.ADDR_PER_ID = ''' || $ROW_ID || ''' --' ,'z')

So I removed the redundant SIEBEL.S_ADDR_PER table.

In addition, when adding : or $AR_H_OPERATION = 'DELETE'
to the record selection condition, lines deleted in SIEBEL.S_ADDR_PER were indeed deleted in the target schema.

I don't quite understand why i needed  to add this condition. I don't think I should have to include deletions in every filter of my CDC tasks in order to cater for deletions