Table of Contents

Search

  1. Preface
  2. Part 1: Introduction
  3. Part 2: Configuring Hub Console Tools
  4. Part 3: Building the Data Model
  5. Part 4: Configuring the Data Flow
  6. Part 5: Executing Informatica MDM Hub Processes
  7. Part 6: Configuring Application Access
  8. Appendix A: MDM Hub Properties
  9. Appendix B: Viewing Configuration Details
  10. Appendix C: Row-level Locking
  11. Appendix D: MDM Hub Logging
  12. Appendix E: Table Partitioning
  13. Appendix F: Collecting MDM Environment Information with the Product Usage Toolkit
  14. Appendix G: Glossary

Rule SQL

Rule SQL

Specifies the condition for the validation rule as an SQL WHERE clause. The load process executes the validation rule. If a record meets the condition specified in the Rule SQL field, the trust value is downgraded by the downgrade percentage that is configured for the validation rule.
The Validation Rules editor prompts you to configure the SQL WHERE clause based on the selected rule type for the validation rule. During the load process, this query is used to check the validity of the data in the staging table.
The following table lists the rule types and contains examples of SQL WHERE clauses for each rule type:
Rule Type
WHERE clause
Examples
Result
Existence Check
WHERE S.ColumnName IS NULL
WHERE S.MIDDLE_NAME IS NULL
Affected columns will be downgraded for records with middle names that are null. Records that do not meet the condition are not affected.
Domain Check
WHERE S.ColumnName IN ('?', '?', '?')
WHERE S.Gender NOT IN ('M', 'F', 'U')
Affected columns will be downgraded if the Gender is any value other than M, F, or U.
Referential Integrity
WHERE NOT EXISTS (SELECT <blank>’a’ FROM ? WHERE ?.? = S.<Column_Name>
WHERE NOT EXISTS (SELECT <blank> 'a' FROM <Ref_Table> WHERE <Ref_Table>.<Ref_Column> = S.<Column_Name>
WHERE NOT EXISTS (SELECT DISTINCT 'a' FROM ACCOUNT_TYPE WHERE ACCOUNT_TYPE.Account_Type = S.Account_Type
Affected columns will be downgraded for records with Account Type values that are not on the Account Type table.
Pattern Validation
WHERE S.ColumnName LIKE 'Pattern'
WHERE S.eMail_Address NOT LIKE '%@%'
Downgrade will be applied if the e-mail address does not contain an @ character.
Custom
WHERE
WHERE LENGTH(S.ZIP_CODE) > 4
Downgrade will be applied if the length of the zip code column is less than 4.

0 COMMENTS

We’d like to hear from you!