Formulas in database fields
Application areas for formulas stored in database fields
With Colbert it is possible to solve problems in SQL that would otherwise have to be solved outside the database for a multiple of cost and completion time.
We have listed some examples below. See how easy problems can be solved now in SQL and realize how difficult, if not impossible, it would be to solve this in SQL without Colbert !
A formula can have a result of any type: for instance, a boolean formula might define criteria to indicate a person’s interest in a particular car.
Colbert SQL with a Formule in the where-clause
Buyer join Cars where Find
With a field Bonus of type <Expr>double , the agreed bonus per year can easily be calculated.
Colbert SQL with a formule in the from-clause
Name, Year, Bonus from Employees natural join Sales where Year between 2016 and 2018
To determine risk scores for a population, two formulas are
defined in the Risk table: a boolean risk criterion and an integer score calculation.
Show the caclulated risk Scores for the matching Crit.
Colbert SQL with a formula in the from-clause and in the where-clause.
Fam.*, AgeOf BirthDate as Age, Code Crit, Code Score, Score from Fam join Risk on crit
Show total risk scores per person.
(Just an aggregation added to the former example).
Colbert SQL with sum over formulas.
Name, Count as Hits, Sum Score from (Name, AgeOf BirthDate as Age, Score from Fam join Risk on crit)
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.
Colbert SQL with First and a Formula in the on-clause.
Name, CohortName from Fam join first (FamCohort order by Rank) on Def