HomeActive Query Builder - IntroductionDesigning user-friendly query building environmentVirtual objects and fields

4.2. Virtual objects and fields

Virtual fields and objects are the feature letting simplify building of queries with the use of frequently used SQL expressions, derived tables and correlated sub queries. The idea is to substitute derived tables for virtual objects which won't differ from ordinary database objects for the end user. Substitution of such objects for derived tables is performed seamlessly for the end user same way as alternate names.

For example, if you have a possibility to save queries for further use in your software, you may add them into the metadata container as virtual objects therefore having provided their repeated use in other queries.

In the same way complex SQL expressions and correlated sub queries may be shown to the end-user as usual fields names. For example, we may add virtual field "Customer" into the Orders table having assigned the expression "(SELECT c.Customer_Name FROM Customers c WHERE c.Customer_Id = Orders.Customer_Id)" for it. Then this expression will be correctly converted and substituted into the final query for execution on the database server.

To get the query text for execution on the database server it's necessary to make an additional PlainTextSQLBuilder component, having set its properties ExpandVirtualFields and ExpandVirtualObjects 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 may add virtual objects and fields to the metadata container in the same way as usual objects and fields, additionally having assigned SQL expression (being substituted by the given field or object) to the Expression property.

This page was: Helpful | Not Helpful