3.4.2.2 Data Validation Tests - SQL Mode

Abbreviations, used in manual:

DV - Data Validation 
JS - JavaScript

The SQL Mode for the DV Test type has been available in quilliup since version 2.6.4.2.

It can be used for RDBMS Data Sources only and it's main advantage is that it significantly improves the runtime of DV Tests, because it's based on SQL Rules/SQL queries. 

The SQL Mode checkbox has been set to "true" by default for RDBMS Data Sets since quilliup version 2.6.5.

How to set SQL Mode?

In the DV Test that uses RDBMS Data Source, choose Test type "Data Validation".

Then, mark the checkbox "SQL Mode" in the upper right corner:

All the Rules will be built in SQL Mode (will use SQL queries). If in current Test previously were JS Rules => they will be automatically disabled.

How to set a Rule in SQL mode:

Name - you can enter name manually or select the "Auto generate name" option (in this case Rule name will be generated automatically).

Description - short Rule description can be added.

Severity - Rule severity can be chosen.

Tags - appropriate Tag(s) can be added to the Rule.

Rule Formula - here you can write your own SQL condition:

• The list of available columns will be shown, if you will start to type [. Column name's should be surrounded with [ ]  For example: to use "id" column you should write "[id]".
• You can use any variable from the related Project by typing "@" sign or by clicking "{:}" sign:
• You can use operands by clicking "</>" sign:
• You can use Calculated Columns and use them by typing "#" sign. 

To create a Calculated Column click "Calculated Columns": and then click "Add New". The following form will be shown:

Choose the Calculated Column name and Type that it will return.

Create the column's Rule and make sure it's valid by clicking "Validate" button. 

To save the new Calculated Column click "Save".

Then, you can use previously created Calculated Column as a part of your Rule. For example: in order to use previously created Calculated Column "id_len" of Type Number, add it to your Rule as follows:

"id_len" is a Calculated Column which we can see in the columns list with "#" sign at the beginning.

"NAME" is a regular column returned from Data set.

Rule body that uses Calculated column will look like: length([NAME])=[id_len]

Pay attention to use [ ] for column's names, and () if you use any SQL function.

When finishing to create the Rule,  check it's validity by clicking "Validate" button.

Threshold - it's possible to set the thresholds values for each Rule separately: 

By clicking  'Edit' for each Rule, you will be able to set the Threshold values:

You can set Rule Threshold in percent or numbers,  with or without Warning.

For example:

In this Test there are three Rules, each Rule has it's own Threshold definitions and gets a different result:

The Test will end as a "Failure" if at least one of it's Rules fails.

The Test will end as a "Warning" if there are no Failed Rules and there is at least one Rule that ended with a Warning status (Successful rules don't affect it) .

The Test will end as "Success" if all of the Rules passed.

For detailed threshold explanation, please see related manual: Test Results - Thresholds & Log Results