Configuring the query of the customer records

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.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here