User Guide

508 Chapter 22: Using Query of Queries
Using aliases
ColdFusion supports the use of database column aliases. An alias is an alternate name for a
database field or value. ColdFusion lets you reuse an alias in the same SQL statement.
One way to create an alias is to concatenate (append) two or more columns to generate a value.
For example, you can concatenate a first name and a last name to create the value fullname.
Because the new value does not exist in a database, you refer to it by its alias. The AS keyword
assigns the alias in the SELECT statement.
Examples
ColdFusion supports alias substitutions in the ORDER BY, GROUP BY, and HAVING clauses.
Note: ColdFusion does not support aliases for table names.
SELECT FirstName + ' ' + LastName AS fullname
from Employee;
The following examples rely on these two master queries:
<cfquery name="employee" datasource="2pubs">
SELECT * FROM employee
</cfquery>
<cfquery name="roysched" datasource="2pubs">
SELECT * FROM roysched
</cfquery>
ORDER BY example
<cfquery name="order_by" dbtype="query">
SELECT (job_id + job_lvl)/2 AS job_value
FROM employee
ORDER BY job_value
</cfquery>
GROUP BY example
<cfquery name="group_by" dbtype="query">
SELECT lorange+hirange AS x, count(hirange)
FROM roysched
GROUP BY x
</cfquery>
HAVING example
<cfquery name="having" dbtype="query">
SELECT (lorange+hirange)/2 AS x,
COUNT(*)
FROM roysched GROUP BY x
HAVING x > 10000
</cfquery>