Expression columns
Expression columns are special kinds of columns provided by InstaDB engine. Their most important feature is the lack of representation on the database level — their values are calculated by InstaDB when they are needed to be displayed to a user or when they are part of some other calculations, like pivot reports or other expression columns.
Expression columns cannot be directly edited bu user, but always represent the current state of database, so they can change to reflect modifications of records and values they depends on.
It is worth to notice that expression columns can be of any format, including reference.
Types of expression columns
There are two types of expression columns you should know:
SQL expression column,
child sum expression column.
The other two types you may find in the Schema Editor (partial sum and previous) are experimental and may not work well or disappear in the future versions of InstaDB.
Child sum expressions
To use child sum expression another table with reference to the current one must be defined. The expression definition requires the following parameters:
-
function which take the values of column indicated in column part of the definition and return the result; basic statistical functions accessible;
-
Vtable is a table with a reference to the currently edited (only tables fulfilling this condition are listed in the selection list);
-
key define which reference column of Vtable is taken into account (as a table may have many references to another one);
-
column which value will be an input for the function; in most cases it will by a numerical column, but for some functions (count, count distinct, minimum and maximum) other data format may return reasonable results.
SQL expressions
SQL expression let to perform any calculation on the database records, including everything what can be expressed with a child sum expression.
Basically the language of expressions is similar to MySQL with some improvements, but also with limitations.
The best way to work with SQL expression is to use built in SQL expression editor, which provides useful features to help you to create valid SQL code efficiently.
Currently the expression editor link is accessible only when a SQL expression column is saved, so it won't appear in the new column form. So the easiest way to enter the editor is to write a column with any valid expression (like "1" for example).
