Using LookCapture in CDC

[vitalf03]

Is it possible to use Source Lookup in the ''Filter'' tab passing the information in ''Record Selection Condition'' using CDC (Change Data Capture)?

[Heinvandenheuvel]

yes

[vitalf03]

how? you can make a simulation? 

i want use it here? you can?

[Heinvandenheuvel · Accepted solution]

Why don't you describe what you are trying to do ?! source_lookup might not be a solution in your case.

Here is a completely contrived - but working - example.

Source table: 'TEST' column ID and some more columns. 7 rows present with ID values : 

SQL> select listagg(id,', ') from test; --->  2000010, 2000011, 2000012, 2000013, 2000030, 2000031, 2000032

Now I create a table to filter on:
SQL> create table my_filter (id number, my_flag varchar(10));
SQL> insert into my_filter values (2000010, 'SKIP');
SQL> insert into my_filter values (2000031, 'SKIP');
SQL> commit;

And in the task, with the help of the Expression Builder I create a Record Selection Condition under Filter:

'SKIP' != coalesce(source_lookup(1000,'ATT_USER','MY_FILTER','MY_FLAG', 'ID=:1',$ID), 'KEEP' )
Note 1)  I used coalesce because I only have rows to skip and want to deal with the 'NULL' returned for rows I want to KEEP.   Note 2) I don't really need the MY_FLAG column.  It's just as example. For my contrived usage I could just return a hardcoded 1 or 0 or the ID itself as for the example only presence or absence count. For you, a special value like a date range, or country code or whatever may be looked up.

After reload the target now has 5 rows with ID values: 2000011, 2000012, 2000013, 2000030, 2000032

Replicate  filtered out the 2 rows with id's 2000010 and 2000031 which were present in the lookup table.

hth,

Hein

[vitalf03]

worked, thank you!