I am writing matching rules, and my data contains the null values. So I would like to know if it is relevant to use sql function coalesce to handle null values in the matching rule ?
I am on PostgreSQL v15 database.
Best regard,
Sylvanus KONAN
Best Answer
S
Stéphanie FOURRIER
said
3 days ago
Hello Sylvanus,
by default in SQL, null=null returns false, which means that if you have null on both record1 and Record2, they will not match by default.
This behavior is usually expected in matching because if all null values match together, you might end up with huge clusters of masters in the same golden record.
If it were your need anyways, to have null match with null, then you can use coalesce(<yourField>, 'A_VALUE'), to have them match anyways, it will work, but you might face performance issues as the indexes that you may have created on the database to improve the performance of matching will not be able to work if you add functions to your attributes.
Thus, the general recommendation in such a case would be to create an enricher to a new technical attribute <yourFieldEnriched>, that will have the coalesce(<yourField>,'A_VALUE') as an expression, and then use this enriched attribute <YourFieldEnriched> used with a direct strict equal in the match rule expression.
I hope my explanation is clear and helps.
Best regards,
Stéphanie.
1 Comment
S
Stéphanie FOURRIER
said
3 days ago
Answer
Hello Sylvanus,
by default in SQL, null=null returns false, which means that if you have null on both record1 and Record2, they will not match by default.
This behavior is usually expected in matching because if all null values match together, you might end up with huge clusters of masters in the same golden record.
If it were your need anyways, to have null match with null, then you can use coalesce(<yourField>, 'A_VALUE'), to have them match anyways, it will work, but you might face performance issues as the indexes that you may have created on the database to improve the performance of matching will not be able to work if you add functions to your attributes.
Thus, the general recommendation in such a case would be to create an enricher to a new technical attribute <yourFieldEnriched>, that will have the coalesce(<yourField>,'A_VALUE') as an expression, and then use this enriched attribute <YourFieldEnriched> used with a direct strict equal in the match rule expression.
Sylvanus KONAN
Hello,
I am writing matching rules, and my data contains the null values. So I would like to know if it is relevant to use sql function coalesce to handle null values in the matching rule ?
I am on PostgreSQL v15 database.
Best regard,
Sylvanus KONAN
Hello Sylvanus,
by default in SQL, null=null returns false, which means that if you have null on both record1 and Record2, they will not match by default.
This behavior is usually expected in matching because if all null values match together, you might end up with huge clusters of masters in the same golden record.
If it were your need anyways, to have null match with null, then you can use coalesce(<yourField>, 'A_VALUE'), to have them match anyways, it will work, but you might face performance issues as the indexes that you may have created on the database to improve the performance of matching will not be able to work if you add functions to your attributes.
Thus, the general recommendation in such a case would be to create an enricher to a new technical attribute <yourFieldEnriched>, that will have the coalesce(<yourField>,'A_VALUE') as an expression, and then use this enriched attribute <YourFieldEnriched> used with a direct strict equal in the match rule expression.
I hope my explanation is clear and helps.
Best regards,
Stéphanie.
Stéphanie FOURRIER
Hello Sylvanus,
by default in SQL, null=null returns false, which means that if you have null on both record1 and Record2, they will not match by default.
This behavior is usually expected in matching because if all null values match together, you might end up with huge clusters of masters in the same golden record.
If it were your need anyways, to have null match with null, then you can use coalesce(<yourField>, 'A_VALUE'), to have them match anyways, it will work, but you might face performance issues as the indexes that you may have created on the database to improve the performance of matching will not be able to work if you add functions to your attributes.
Thus, the general recommendation in such a case would be to create an enricher to a new technical attribute <yourFieldEnriched>, that will have the coalesce(<yourField>,'A_VALUE') as an expression, and then use this enriched attribute <YourFieldEnriched> used with a direct strict equal in the match rule expression.
I hope my explanation is clear and helps.
Best regards,
Stéphanie.
-
Can we reset Matches and run again on match rule change or add a new match rule?
-
"Unmerge" records
-
Turn off match rules to speed up an integration job
-
Can anyone tell me how to load a Fuzzy-Matched entity ... but skip the matching happening auto-magically?
-
Importing CSV in Fuzzy Matched Entity Does Not Trigger Consolidation
-
How can I trigger a "match on child records"?
-
How can I Configure Most Frequent Values in Survivorship Rules?
-
Deterministic or probabilistic matching
-
Machine Learning and AI for matching
-
Prevent loads from replacing values overridden by users
See all 53 topics