Using SQL Functions to query Databases and Using the Queries in Data Generation Functions

This process is useful for building complex SQL functions that can then be used for Data Generation. The example below shows how to use an SQL function to calculate a value from Database constituents that use that value. Again, the following examples are done with respect to the SampleCommerce Database.

Total Amount Example using an SQL Function

This example describes how to use an SQL Function to calculate the Total Amount of an Order.

When we create an order, we don’t know how many order items we will have or the price of the order item and Amount ( OrderItem * Price)

So we should do the following

1. Generate the XML (Parameters sheet)
2. Create the Unit Price

To sum-up the value of a particular order item/UnitPrice, we can use an SQL function such as the one highlighted in the FunctionParameters column below:

Another way to do this is to use a PreProcessVariable where before generating any Order, we query the DB for existing products and we’ll use one such product.  This can be a little slow because SQL is executed every time we add an OrderItem.

So, instead we can Query all available Products or Random Products from the Database at once then use that particular product while Generating the OrderItems.