IEEE Spectrum just published the article “The Rise of SQL” about the recent SQL’s comeback caused not only by the ever-increasing use of databases, but also by the use of SQL within the fields of data science, machine learning, big data, and streaming systems. While traditionally, Business Rule Engines did not communicate with databases directly, our customers frequently prefer to use SQL-like business rules to access their data when it is necessary following their business logic. At the same time, they want to preserve the power of SQL dealing with databases of any complexity. Two years ago OpenRules introduced a special product “Rule DB” that does exactly this by empowering Excel-based business rules with a run-time RDBMS communication mechanism. In this post we will explain how to migrate an SQL query to OpenRules.

Let’s assume that you already installed a free MariaDB (or MySQL) and downloaded its Sample Database. Here is an example of an SQL query that works with three tables “customers”, “orders”, and “payments”:

When we execute this SQL query, we receive the following results:

Technical Logic. Note that the WHERE part of this query contains a special condition

If we remove it, the query will produce many records with NULL order’s status and payment’s amount. This knowledge along with two joins constitutes “technical logic” of the database organization.

Business Logic. Alternatively, the condition

constitutes “business logic” of the above query that contrary to the technical part, could be frequently changed by business users.

Migration. Our objective is to migrate any SQL queries (much more complex than the above one) to business rules and give business people control over business logic. First we will represent the above SQL query in a special OpenRules table of the type “DataSQL” that allow a user to define a technical part of the above SQL query:

Now we will represent the business part of the same query in the regular decision table:

We want to execute these rules for all “SelectedRecords”, so we will call it for the table:

The column “ActionIterate” directs OpenRules engine to iterate over all records defined in the above table “SelectedRecords” and for each records execute the rules “DefineTotals”. To glue everything together, as usual we need to specify the Glossary:

This glossary for the business concept “SelectedOrders” specifies the business names of the attributes defined in our DataSQL table “SelectedOrders”. 

Now we can execute our decision model by a click on “Test.bat”. Here are the execution results:

You may find more complex SQL queries migrated to business rules by downloading RuleDB and start integrating your own SQL with business rules.

Conclusion. OpenRules RuleDB shows that Business Rules and SQL don’t have to be competitors but rather partners complementing each other. RuleDB does exactly this by allowing business analysts who used to work with separate SQL queries to migrate them to business rules. Without sacrificing the SQL power, business users can concentrate on the business logic by defining it in the friendly, easily modifiable decision tables.