1.1

• They are used in the ORDER BY CLAUSE as the column names available for sorting.
Example
-- this example shows SELECT-FROM-WHERE
-- with an ORDER BY clause
-- and correlation-Names for the tables
SELECT CONSTRAINTNAME, COLUMNNAME
FROM SYS.SYSTABLES t, SYS.SYSCOLUMNS col,
SYS.SYSCONSTRAINTS cons, SYS.SYSCHECKS checks
WHERE t.TABLENAME = 'CUSTOMERS' AND t.TABLEID = col.
REFERENCEID AND t.TABLEID = cons.TABLEID
AND cons.CONSTRAINTID = checks.CONSTRAINTID
ORDER BY CONSTRAINTNAME
-- This example shows the use of the DISTINCT clause
SELECT DISTINCT CID
FROM TRADE.PORTFOLIO
-- This example shows how to rename an expression
-- List number of customers holding a security
–- average quantity of security ,
-- the security ID,
-- for all securities in the PORTFOLIO table.
-- Arrange the result table in ascending order by average
quantity.
SELECT COUNT(*) AS NUM_CUSTOMERS,SID, AVG(QTY) AS AVG_QTY
FROM TRADE.PORTFOLIO
GROUP BY SID
ORDER BY 3
TableExpression
A TableExpression specifies a table, view, or function in a FROM CLAUSE. It is the source from which a
SelectExpression selects a result.
Syntax
{
TableView | JOIN Operations
}
Description
A correlation name can be applied to a table in a TableExpression so that its columns can be qualified with that
name. If you do not supply a correlation name, the table name qualifies the column name. When you give a table
a correlation name, you cannot use the table name to qualify columns. You must use the correlation name when
qualifying column names.
No two items in the FROM clause can have the same correlation name, and no correlation name can be the same
as an unqualified table name specified in that FROM clause.
In addition, you can give the columns of the table new names in the AS clause. Some situations in which this is
useful:
• When a VALUES expression is used as a TableSubquery, since there is no other way to name the columns of
a VALUES expression .
• When column names would otherwise be the same as those of columns in other tables; renaming them means
you don't have to qualify them.
The Query in a TableSubquery appearing in a FromItem can contain multiple columns and return multiple rows.
vFabric SQLFire User's Guide528
vFabric SQLFire Reference