Adding SQL Criteria

Search Knowledge Base by Keyword

< Back

To create new SQL Test Criteria, click on ‘Catalogues’ under the ‘Data Catalogue’ tab and create a new Test Criteria.

Adding SQL Criteria in Test Modeller

This will display the popup shown below, here you can enter a unique and recognisable criteria name and description. Once you have entered these fields, click save, if the Test Criteria already exists you will be prompted to rename it.

Test Criteria Naming

Criteria

Once these details have been complete, we can move on to the ‘Criteria’ tab. The form requires you to split up the SQL into components that will be reassembled by the Data Allocation process when run.

SQL Critera

These are:

  • Table Name: The name of the table.

You can have multiple table names here if you wish and include aliases. This value guarantees uniqueness, for more on uniqueness, see the section Test Data Allocation Pools.

  • Group By: The group by clause, if you need to group columns as part of your selection.
  • Order by: The Order by clause, you may use this if you want to find values sorted by values, for example find the latest TRANSACTION_ID for a recently closed account.
  • SQL Criteria: The where clause inside the SQL.

Before you start filling in the criteria make sure you have run the SQL against your test database. Here are three examples of SQL:

SQL Examples

The First is showing you how to build criteria to find opportunities. You can see that we have specified exact values to make sure the query works.

The Second has been edited to replace the hard-coded values with wild cards. These are identified with a percentage % and a number, 1,2,3 etc. These will be replaced at allocation time by values that your specific test case need.

You can see a technique where it is possible to pass in an empty parameter ‘OR ‘%1’ =’’’

This allows you to call the same query with one, two or three parameters set.

The Third is combining a number of fields together into a string, each RDBMS will use different syntax to accomplish this, Oracle for example uses ‘¦¦’  whilst most others use ‘+’.

In all three we will be returning specific columns that will be the data that is needed by the test case. The test case will then enter these returned values into the UI, API, Flat file etc.

These returned columns of data will be defined in the Keys and Parameter tabs.

Advanced

If you are converting non character datatypes you may wish to use the advanced feature to manually enter the string.

SQL Criteria Advanced

To do this, click on Advanced. This will be, by default, set by Test Modeller when it runs, however if there is a problem with the syntax that is created by Test Modeller or you just want to adjust the syntax, edit the values here and click save.

Expected results SQL:  

This is an additional feature that looks up an expected result from a back-end database; this is often useful when using automated and manual test cases. Include additional columns here to capture extra values beyond what would normally be captured by the existing keys that are used to identify uniqueness. The same WHERE criteria to identify location will be issued for both allocated and expected results.

An example would be:

The key SQL: CAST(SalesOrderNumber as VARCHAR) + ‘—‘ + CAST(SalesOrderLineNumber as VARCHAR)

The expected result SQL: cast(sum(SalesAmount – TotalProductCost) as varchar)

The expected result would now be calculated and returned using the same keys as allocated.

Reading Multiple Columns

Sometimes you may wish to retrieve lots of data from different columns using your query.

Concatenating multiple columns

Data Modeller requires that column data be returned as a varChar datatype in one string. Multiple columns must be concatenated together and separated by ‘—’.

In the example above there are three columns concatenated, these will be identified as:

LeadName (We’ve merged in the title, first name and last name)

There is also a second column ID which has been concatenated but separated by ‘—’ this will be split out in Modeller into a second retrieved value, this will be identified by LeadID.

These output column names will be entered in the ‘Keys’ tab, see later.