Virtual Fields and Objects lets simplify visual building of queries in case when same derived tables, correlated sub-queries or SQL expressions are used fre derived tables and correlated sub queries. Active Query Builder allows to represent such entities as Virtual objects and Fields. Virtual entities aren't differ from ordinary database objects and fields, the end-user works with them as if they were real objects and fields.

Substitution of virtual entities for containing expressions is performed seamlessly same way as alternate names.

Virtual Object acts like a database view. It holds a SELECT query inside, so the Virtual Object name is substituted for containing query and becomes a derived table when executing such query against the database server.

Virtual (calculated) field can represent complex SQL expressions and correlated sub queries may be . For example, one can add the "Customer" virtual field to the "Orders" table containing the following SQL expression: "(SELECT c.Customer_Name FROM Customers c WHERE c.Customer_Id = Orders.Customer_Id)". Being selected in the query, this field will display the customer's name in resultset.

Using this feature you should use two instances of the PlainTextSQLBuilder: one to get the query text for editing by the end-user and another to get the query for execution against the database server, having set the ExpandVirtualFields and ExpandVirtualObjects properties to True. As opposed to alternate names, where real names in the query may be converted into alternate ones and inversely, inverse conversion of SQL expressions into virtual objects and fields is impossible.

You can add virtual objects and fields to the Metadata Container the same way as usual objects and fields, additionally assigning SQL expression text to the Expression property of MetadataField and MetadataObject objects.

Is this article helpful for you?