I have this scenario:
Source node:
Schema1:
- Table1: id, field1, field2
- Table2: id, table1_id, table3_id
- Table3: id
- Table4: id, table1_id
Schema2:
- Table5: id, table3_id, table6_id, field3, field4
- Table6: id
Target Node:
Schema1:
- Table7: id, field1, field2, field3, field4.
To solve this, I have created one router of "default" type, triggers for each table and the corresponding rows in trigger_router table. After that, I have created one row in transform_table to manage transformations from Table1 in source node, to Table 7 in target node. Here is my problem: At first, I tried to create one row in transform_table for getting data from Table6 to Table7, but I can't use any primary Key to link Table1 and Table6 in a direct way in the source. Now I'm trying to use lookup transformation to get field3 and field4.In order to achieve that, I have created a row in transform_column table like this:
TARGET_COLUMN_NAME: field3
SOURCE_COLUMN_NAME: null
PK: 0
TRANSFORM_TYPE:lookup
TRANSFORM_EXPRESSION:
SELECT field3
FROM schema1.table1 s1t1
INNER JOIN schema1.table2 s1t2 ON s1t2.table1_id = s1t1.id
LEFT JOIN schema1.table3 s1t3 ON s1t3.id = s1t2.table3_id
LEFT JOIN schema2.table5 s2t5 ON s2t5.table3_id = s1t3.id
LEFT JOIN schema2.table6 s2t6 ON s2t6.id = s2t5.table6_id
WHERE s1t1.id = :ID
I understand that, when transformation take place the :ID variable will be replaced with the id of the table1 row that I'm getting. The problem I'm having is that field3 and field4 could be NULL in some table7 rows (as you can imagine from the LEFT JOINS in query). So I'm getting the error
Expected a single row, but returned no rows from lookup for target column field3 on transform source_2_target_table7_table1_schema1
Is there any way to force SymmetricDS to copy a NULL value in this column when lookup expression returns no rows? Or is there any other way to achieve this kind of synchronization?
Thanks in advance
