Adding SQL Criteria – Data, Keys and Parameters

Search Knowledge Base by Keyword

< Back

Data

Select the ‘Data’ tab:

SQL Criteria Data

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:

SQL Criteria - Key

Click on ‘New Key’ to enter the output columns from the SQL.

SQL Criteria Key 2

In the example above we have added in the names of our retrieved values. You have two options:

  1. 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.
  2. 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:

SQL Criteria Parameters

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:

SQL Parameters 2

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.

SQL Parameters 3

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’