Wonach suchst Du?
What are you looking for?

Virtual Columns

Overview

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.

Example

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 select statement.

When creating or changing SQL columns they are validated on save

Each time the AD_Column.ColumnSQL is changed, the code is validated on save.

The validation goes as follows:

  1. Selecting the ColumnSQL code, we try to replace the context table name with the master alias (same as we do in production code).
  2. The following query is executed:
    select
    ( your AD_Column.ColumnSQL code using "master" alias )
    from TableName master
    limit 1
    
  3. If that code succeeds ➔ OK, if not ➔ ERROR.

Dos and Don’t

  1. When using select statements, add parentheses () around them, e.g., (select value from other table where...).
  2. Never use slashes / in column names even though they are virtual.

How to avoid some corner-case issues

Definition of a “corner case”:

A corner case occurs when ColumnSQL has a subquery which joins the same table again.

Hint for developers:
Consider using @JoinTableNameOrAliasIncludingDot@ instead of the actual table name, at least for the corner cases.

How to use @JoinTableNameOrAliasIncludingDot@?

Example

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:

To avoid this, you could write:

( SELECT p.Value FROM c_elementvalue p where p.c_elementvalue_id=@JoinTableNameOrAliasIncludingDot@parent_id)

Please note:


View source file on GitHub.com