Creating New SQL Test Criteria
Table of Contents:
Adding SQL Criteria
To create new SQL Test Criteria, click on 'Catalogues' under the 'Data Catalogue' tab and create a new Test Criteria.
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.
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.
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:
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.
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.
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.
Data, Keys and Parameters
Data
Select the ‘Data’ tab:
Default How Many: Enter how many values you wish to return; this defaults to 1. You can request to have multiple values, for example, I can request to get 10 orders for a specific customer. This value can be overridden for specific test cases, the default is set when you create a new Test in an allocation pool.
Default Unique: This will be the default for the allocated pool test case, it can be overridden. Setting this means that any retrieved values will not be used by any other tests within the allocation pool.
Use to Make Data if no data found: This allows you to invoke a VIP flow if the allocated test returns no data. This feature means that if no data exists you can run ANY process you like to create the data. This is especially useful for automated testing whereby you would normally have to skip the test if there was no appropriate data.
N.B. The input parameters of the make VIP flow must be in the same order as the input parameters of the Find Test, see ‘Using VIP to Make Data’
Keys
Select the ‘Keys’ tab:
Click on ‘New Key’ to enter the output columns from the SQL.
In the example above we have added in the names of our retrieved values. You have two options:
Enter the SQL columns names exactly as used in your SQL and the column Key Names SQL Override will be populated for you, if it is not set.
Enter textually descriptive names and then enter the SQL into the Key Names SQL Override yourself.
Start with first method when you begin setting up tests.
Parameters
Select the ‘Parameters’ tab:
The parameter tab allows you to give logical names to the inputs defined in the SQL Criteria, the %1, %2, %3 values defined earlier. It also allows you to define logical names for your output data, these names will be used inside Test Modeller when building models that need test data. It is much easier to identify a value such as ‘Customer Full Name’ rather than ‘CstFn’ for example.
Click on the New Parameter button:
The ‘In’ direction maps the %1, %2 values in the criteria. Give each of these descriptive names.
The ‘Out’ direction maps the key values in the previous tab, you can give these different logical names.
In all cases the order is important, once you have created the parameters you can click on a row and move it up and down by dragging it.
The example above shows a %1 being converted to the column name EmailOptin & Two output columns being returned by the Key Name SQL Override.
Click on Save once you have filled in all the tabs.
Create a simple manual test in an allocation pool and run the test to make sure the SQL is being parsed correctly. See the section ‘Running The Test Data Allocation’
TEST DATA AUTOMATION TEST DATA ALLOCATION SQL CRITERIA