Skip to main content Skip to complementary content

Creating an SQL business rule

SQL business rules can be simple rules with WHERE clauses. They can also have join conditions in them to combine common values between columns in database tables and give a result dataset.

Creating the business rule

Procedure

  1. In the DQ Repository tree view, expand Libraries > Rules.
  2. Right-click SQL.
    Contextual menu of an SQL rule.
  3. From the contextual menu, select New Business Rule to open the New Business Rule wizard.
    Consider as an example that you want to create a business rule to match the age of all customers listed in the age column of a defined table. You want to filter all the age records to identify those that fulfill the specified criterion.
  4. In the Name field, enter a name for this new SQL business rule.
    Information noteImportant:

    Do not use the following special characters in the item names: ~ ! ` # ^ * & \\ / ? : ; \ , . ( ) ¥ ' " « » < >

    These characters are all replaced with "_" in the file system and you may end up creating duplicate items.

  5. Optional: Set other metadata (Purpose, Description and Author) in the corresponding fields.
  6. Click Next.
  7. In the Where clause field, enter the WHERE clause to be used in the analysis.
    In this example, the WHERE clause is used to match the records where customer age is greater than 18.
  8. Click Finish to close the New Business Rule wizard.
    A sub-folder for this new SQL business rule is displayed under the Rules folder in the DQ Repository tree view. The SQL business rule editor opens with the defined metadata.
    Overview of the business rule settings.
    Information noteNote: In the SQL business rule editor, you can modify the WHERE clause or add a new one directly in the Data quality rule view.
  9. If required, set a value in the Criticality Level field.

    This will act as an indicator to measure the importance of the SQL business rule.

Creating a join condition

About this task

This step is optional. You can decide to create a business rule without a join condition and use it with only the WHERE clause in the table analysis.

For an example of a table analysis with a simple business rule, see Creating a table analysis with a simple SQL business rule. For an example of a table analysis with a business rule that has a join condition, see Creating a table analysis with an SQL business rule with a join condition.

Procedure

  1. In the SQL business rule editor, click Join Condition to open the corresponding view.
  2. Click the [+] button to add a row in the Join Condition table.
  3. Define the join condition and save it.
  4. Expand the Metadata folder in the DQ Repository tree view, and browse to the columns in the tables on which you want to create the join condition.
    This join condition will define the relationship between a table A and a table B using a comparison operator on a specific column in both tables. In this example, the join condition will compare the "name" value in the Person and Person_Ref tables that have a common column called name.
    Information noteNote: You must be careful when defining the join clause. In order to get an easy to understand result, it is advisable to make sure that the joined tables do not have duplicate values. For further information, see Creating a table analysis with an SQL business rule with a join condition.
  5. Drag-and-drop the columns from the DQ Repository tree view to the Join Condition table.
    A dialog box is displayed prompting you to select where to place the column: in TableA or in TableB.
  6. Select a comparison condition operator between the two columns in the tables and save your modifications.
    In the analysis editor, you can now drop this newly created SQL business rule onto a table that has an "age" column. When you run the analysis, the join to the second column is done automatically.
    Information noteWarning: The table to which to add the business rule must contain at least one of the columns used in the SQL business rule.

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 – please let us know!

Leave your feedback here