Procedure
- Double-click tDBInvalidRows to open its Component view.
- In the Validation type field, select DQ rule validation in order to use the given data quality rule.
- Select Use an existing connection to reuse the connection that tDBConnection creates.
-
Click the [...] button next to Edit schema to open the schema editor.
- Click the [+] button three times to add throw rows and rename them as Name, DOB and Email, respectively.
- In the DOB row, select VARCHAR in the DB type column, enter 19 in the Length column and 0 in the Precision column.
- Click OK to validate these changes and propagate the changes.
- In the Table name field, browse to or enter the database table to be checked, customers in this example.
-
In the Where clause field,
enter the clause to be used, in addition to the given data quality rule, to query the
customer data.
In this example, enter
`customers`.`Email` like 's%'
in quotation marks to retrieve the email records beginning with the letter s or S. -
In the DQ rule list field,
select the data quality rule to be used. In this example, select customers_age. This rule is a demo rule reading
18<=((TO_DAYS(NOW())-TO_DAYS(DOB))/365)
You can check the available data quality rules in the DQ Repository, Libraries > Rules > SQL.For further information, see Importing a data quality demo project. - Click Guess Query to generate the corresponding query clause that uses the Not clause.
-
In the Query field, change
18 to 20 to modify the clause as the
following:
"SELECT `Name`, `DOB`, `Email` FROM `crm`.`cust` WHERE (NOT ( 20>((TO_DAYS(NOW())-TO_DAYS(DOB))/365) ) AND `cust`.`Email` like 's%')"
This clause lets you select the customers whose age is 20 or more.