SQL and VIP Lookups

Search Knowledge Base by Keyword

< Back

When you define a test type there are two types, the first is SQL and the other, is a VIP(Flow):

SQL Lookups typically return lists of columns from tables, these are used to define uniqueness so they can be eliminated as already being used. For example, Test 1 needs a USD Credit card account. Whilst Test 2 needs an account with a fraudulent transaction. These lookups could be expressed as:

SELECT CARD_ID FROM CARD WHERE CURRENCY IS ‘USD’

SELECT CARD_ID FROM CARD WHERE CARD_ID IN (SELECT CARD_ID FROM TRANS WHERE FRAUD = ‘Y’)

When the SQL is defined, the Table Name and the List of Columns act as the logical key that will identify whether another test is already using the same combination of columns.

For the example above, within an allocation pool, when looking for a CARD_ID for Test 2, the value that has been allocated with a TABLE_NAME of CARD and key name of CARD_ID will be checked. Therefore, when looking for the currency USD for Test 1, the allocated value will not be used by Test 2 when looking for CARD_IDs.

VIP Lookups are the second type of lookup here. VIP is a powerful Integration engine that can make call outs via multiple methods to find and make data. See the section ‘Using VIP to Lookup and Make Data’. When defining the VIP flow in Test Modeller you still need to be able to segment different flows, so they do not use logical keys from other flows (or SQL). Consequently, when you define a flow, you need to enter a Data Identifier as well as the list of keys.

For example, you may have two tests:

Test 1 which retrieves a CUSTOMER_NUMBER using a rest call to SalesForce. Here, the Data Identifier is set to SFCUSTOMER and Key CUSTOMER_NUMBER.
Test 2 could read a flat file and pass a list of multiple CUSTOMER_NUMBER from the file. If you set the Data Identifier to the same value, SFCUSTOMER, then Test 2 will not use any values from the file which have already been allocated by the rest call in Test 1.

When you set up an allocation pool any values assigned to any manual or automated test cases that call the pool are stored within that pool. Remember, each test environment you are working with will have different data in it so you will get different allocated values in each pool if you set up a specific pool for each individual test case and development environment.

For automated tests it is possible for them to run in multiple environments, in this case, you will need to create a pool for each environment. Developers may also have their own personal test databases so they would need to set up their own allocation pools.

There is a batch utility to export and import Test Data Allocation Pools, contact us if you need to use this.