SELECT (Virtual Columns)
Besides supporting standard SQL SELECT statements, TDV supports the definition of “virtual columns” in the projection list for a view. After virtual columns are declared, you can use them in a query anywhere that you can use a literal.
The primary use of a virtual column is in procedures included in the FROM clause of a query. However, you can also use virtual columns in WHERE, HAVING, and JOIN ON clauses. Including them in the GROUP BY and ORDER BY clauses is acceptable, but it has no effect (like literals).
Syntax
{DECLARE columnName columnType [DEFAULT literalValue]}
The virtual column is declared in the SELECT clause, as follows:
SELECT c1, {DECLARE columnNameA columnTypeA,
c2, {DECLARE columnNameB columnTypeB DEFAULT xx} ...
Remarks
• Virtual columns are unqualified, so their names must be unique and different from the names of items in the FROM clause.
For example, if you select FROM a table with a column named ColumnOne, the virtual column should not be named ColumnOne.
• When a query using virtual columns is executed, the query engine analyzes the predicates (such as a WHERE clause) to look for columnName = literal expressions. These clauses are removed from the query and the literal is replaced, much like a ? (question mark) is replaced in a prepared statement.
For example, the following statement
SELECT * FROM V1 WHERE columnName = 99
would become
SELECT T1.column1, 99, T1.column2
FROM /some/table T1, Procedure1 (5,99) P1, Procedure2 (concat(99,'abc')) P2
WHERE (99 > T1.column1) AND (T1.someKey = P2.someKey)
• The use of columnName = literal is important. Other types of comparison operators do not result in setting the value. The literal can be a single literal or an expression containing only functions and literals, like concat('abc','def').
• Relationship optimization applies to virtual columns. This means that if the query has columnName = otherColumn and there is a predicate for otherColumn = 5, the query engine figures out that columnName = 5 is also true and set that for you.
• It is possible when using outer joins for the WHERE clause to be illegally applied to the inner side of the join. When this happens, the query engine is unable to do the replacement, resulting in an error message that may or may not be easy to understand.
• If no DEFAULT value is specified for a virtual column, the column’s value must be specified in the WHERE clause; otherwise, an error occurs.
• If a DEFAULT value is specified, it is used if no WHERE clause setting is found.
• If a virtual column is set to more than one value, you get an error.
Example
The following SELECT statement defines view V1:
SELECT T1.column1, {DECLARE columnName INTEGER DEFAULT 50}, T1.column2
FROM /some/table T1, Procedure1 (5, columnName) P1, Procedure2 (concat(columnName,'abc')) P2
WHERE (columnName > T1.column1) AND (T1.someKey = P2.someKey)