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>

View comments in LiveDocs