Virtual Fields and Objects lets simplify visual building of queries in the case when same derived tables, correlated sub-queries or SQL expressions are frequently used in other queries. Active Query Builder allows adding such entities to the Metadata Container and using them in queries the same way as regular database objects and fields.
Virtual Objects act as user-defined database views. They hold a SELECT query inside, so the Virtual Object name is substituted for containing query and becomes a derived table when it comes for executing such query against the database server.
End-users don't see this substitution, they work with SQL queries containing virtual objects: build them visually, edit SQL query text via a text editor.
Virtual fields can stand for complex SQL expressions (calculated fields), and correlated subqueries.
You can get the SQL query text either with virtual entity names or substitutional SQL expressions at any time:
- with substitutional SQL expressions to execute against a database server,
- with virtual entity names to display and let edit it by end-users.
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)
Mention the "Orders" reference which must be added to establish the correlation with the Orders table to which this field is added to. When the "Orders.Customer" field is used in the query, the user gets the customer name in the query result set.
Note: Sub-queries in expressions of virtual entities must be enclosed in parentheses!
Note: Any object or field in the Metadata Container becomes virtual when an expression is defined for it, so you can replace real objects with virtual ones to bring in additional functionality to them.
Note: As opposed to the Alternate Names feature, where real names in the query could be converted into alternate ones, and vice versa, the inverse conversion of SQL expressions into virtual objects and fields is not possible.
Component Setup (.NET Edition)
The SQLGenerationOptions and its inheritor SQLFormattingOptions classes used to define SQL generation rules, have the ExpandVirtualObjects and ExpandVirtualFields properties. They determine whether to expand the corresponding virtual entities into their expressions or not.
Thus, the QueryBuilder.SQLGenerationOptions property determines rules for the QueryBuilder.SQL property and the QueryBuilder.SQLFormattingOptions for the QueryBuilder.FormattedSQL property. We recommend setting the SQLGenerationOptions.UseAltNames to false to get the SQL for execution against the database server, and the SQLFormattingOptions.UseAltNames to true to display the query with friendly names to the user.
Component Setup (VCL Edition)
To work with this feature, you must use two instances of the PlainTextSQLBuilder object: 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.
Defining virtual objects and fields
Virtual entities can be defined in several ways.
Via the "expression" tag of fields and objects in the saved metadata XML file. You can use the Metadata Editor tool (as a standalone utility or by calling it from code) to create and edit metadata XML files.
Adding an object or field to the Metadata Container by code by assigning the substitutional SQL expression to the Expression property of MetadataObject and MetadataField objects.