Getting Data Using a SQL Query

Using a SQL query within rules and calculations is a powerful way to combine database functionality with rules. This functionality was designed for you to:

• Lookup and evaluate individual values, records or sets of records from database tables
• Load database data into entities and collections
• Modify database data from rules via stored procedures or transactional queries

Like other types of functionality in InRule once the SQL query and database connection is setup by a technical individual, it can be relatively easy for a business analyst to use the query within rules and calculations.

In this example you’ll see a different way to perform the lookup that was performed in the previous TableLookup example. There are two reasons to use a SQL query over a Table Lookup for this scenario:

• The rule authors have little or no understanding of database concepts and/or the underlying database table(s)
• The underlying table is relatively large so using a SQL query may yield better performance

To recap the scenario from the previous example, you want to see if an order qualifies for free shipping based on the customer’s loyalty status by looking up the FreeShipping flag in this table:

If a SQL query called GetFreeShippingStatus has been created in the rule application, the rule author could use it in a rule or calculation. SQL queries are often setup to take parameters to narrow the results. Following is the query for this example that takes one parameter:

Example of SQL query in business language

Example of SQL query in syntax

This example uses this SQL query:

Click on these links for more information on setting up SQL Queries and Database Connections