Virtual columns are columns that retrieve data via SQL and are not persistent in the database.
They are executed together with the
select statement of the data source.
This is a very, very simplified symbolic query run for the invoice window to show you where the SQL of a virtual column is actually executed:
select documentno, dateinvoiced ... from c_invoice
Now we are going to add a virtual column to that window. It is embedded like this:
select documentno, dateinvoiced, ... <some virtual column SQL>, ... from c_invoice
So, in order to make your SQL code work in the virtual column it needs to be runnable as a column in a
Each time the
AD_Column.ColumnSQL is changed, the code is validated on save.
The validation goes as follows:
ColumnSQLcode, we try to replace the context table name with the
masteralias (same as we do in production code).
select ( your AD_Column.ColumnSQL code using "master" alias ) from TableName master limit 1
selectstatements, add parentheses
()around them, e.g.,
(select value from other table where...).
/in column names even though they are virtual.
A corner case occurs when
ColumnSQL has a subquery which joins the same table again.
|Hint for developers:|
Assume that in the “Account” window (aka “Element value”,
window/540761) you want to show the account number of a parent account.
You might be tempted to write:
( SELECT p.Value FROM c_elementvalue p where p.c_elementvalue_id=C_ElementValue.parent_id)
which in this case would only work because you paid attention to the following details:
...FROM c_elementvalueyou wrote the table name with lowercase letters which will be ignored by our parser when it tries to replace the table name with the
...p.c_elementvalue_id=C_ElementValue.parent_idyou wrote the parent link table, with
C_ElementValuerespecting the letter case. Consequently it will be replaced with
masterby the parser when the bigger SQL is built.
To avoid this, you could write:
( SELECT p.Value FROM c_elementvalue p where p.c_elementvalue_id=@JoinTableNameOrAliasIncludingDot@parent_id)
FROM c_elementvalueto make sure the table name is not replaced with