Application areas for formulas stored in database fields

Colbert allows to solve problems in SQL that otherwise wouldhave to be solved outside the database for a multiple of cost and completion time.

We have listed a couple of examples below. See how easy problems can now be solved in SQL and realize how difficult, if not impossible, it would be to solve this in SQL without Colbert !

Criteria matching

Find a match between buyers and available cars

A formula can have a result of any type: for instance, a boolean formula Find can define criteria to indicate a person’s interest in a particular second hand car.

When a formula is called in a SQL query, it is compiled once and parameters are bound to expressions found in the context
of that query.

The challenge: Show which car matches the search criteria for every buyer.

The Buyer table is defined as: create table Buyer(Name string, Find Expr<bool>)

No further SQL extensions are needed.

The calculated value of Find is used in the where-clause to find a match.

Colbert SQL with a Formula in the where-clause:

Buyer join Cars where Find

Bonus calculation

Calculate the individual annual employee bonus

With a field Bonus of type <Expr>double , the agreed annual employee bonus can easily be calculated.

The challenge: Show the individual annual employee bonus.

Colbert SQL with a Formula in the from-clause:

Name, Year, Bonus from Employees natural join Sales where Year between 2016 and 2018

Risk analysis

Evaluate risk scores in e.g. insurance or banking industry

To determine risk scores for a population, two formulas are defined in the Risk table: a boolean risk criterion and an integer score calculation: If the criterion holds, the score contributes to a risk.

The challange: Show the caclulated risk Scores for the matching Crit.

The Risk table is defined as: Create table Risk (Crit Expr<bool>, Score Expr<int>)

As table Fam has no Age field, it is defined in the from-clause as AgeOf BirthDay

The operator Code shows the formula itself instead of the calculated value.

Colbert SQL with a Formula in the from-clause and a Formula in the where-clause:

Fam.*, AgeOf BirthDate as Age, Code Crit, Code Score, Score from Fam join Risk on crit

Cohort definition

Divide a population into cohorts based on a formula

Sometimes it is not precise enough to divide a population into cohorts based on field values. Sometimes you want to divide a population into several cohorts by assigning characteristics based on formulas.

With Colbert you can apply these formulas with a SQL join to a population to get the right cohort division.

The challange: Show cohorts

In this example a person is assigned to exactly one cohort based on the matching formula with the lowest Rank. In Colbert this can be done by the First operator.

Colbert SQL with the First operator and a Formula in the on-clause.

Name, CohortName from Fam join first (FamCohort order by Rank) on Def