SymmetricDS: lookup Table Router route to common and specific node

Question

In SymmetricDS I know that 'lookuptable' in SYM_ROUTER can sync to specific node by referring a column that has the node id.

What I would want is for common data to sync to all node, how to configure this?

Example : I have 2 tables, Table 1 is Item, Table 2 is Store

ITEM_ID | ITEM_NAME | BRAND_ID

Item 1 | name | A

Item 2 | name | B

Item 3 | name | C

BRAND_ID | STORE_ID

A | ALL

B | 001

C | 002

I want to sync Item 1 to ALL nodes, Item 2 to node 001, Item 3 to node 002, etc. I will use below router to sync Item table. Item 2 and Item 3 are synced OK, but Item 1 fail to sync to all node, do you have any way to sync Item 1 ?

insert into SYM_ROUTER (
 router_id, source_node_group_id, target_node_group_id, router_type, router_expression, create_time, last_update_time
) values (
 'corp-2-store-ok','corp', 'store', 'lookuptable', 'LOOKUP_TABLE=STORE KEY_COLUMN=BRAND_ID LOOKUP_KEY_COLUMN=BRAND_ID EXTERNAL_ID_COLUMN=STORE_ID', current_timestamp, current_timestamp
);

Answer 1

You'll need a router with the router_type 'subselect' and router_expression something like

c.external_id in (
  select :STORE_ID from dual
  union all
  select external_id from sym_node where 'ALL' = :STORE_ID 
)