A Query of Queries requires that every column have metadata that defines the column's data type. All queries that ColdFusion creates have metadata. However, a query created with QueryNew function that omits the second parameter does not contain metadata. You use this optional second parameter to define the data type of each column in the query.

Specify column data types in the QueryNew function

Specify the column data types in the QueryAddColumn function

<!--- Make a query. --->
<cfset myQuery = QueryNew("")>

1. Add and populate a column with the QueryAddColumn function, specifying the data type in the third parameter:

<!--- Create an array. --->
<cfset FastFoodArray = ArrayNew(1)>
<cfset FastFoodArray[1] = "French Fries">
<cfset FastFoodArray[2] = "Hot Dogs">
<cfset FastFoodArray[3] = "Fried Clams">
<cfset FastFoodArray[4] = "Thick Shakes">
<!--- Use the array to add a column to the query. --->
<cfset nColumnNumber = QueryAddColumn(myQuery, "FastFood", "CF_SQL_VARCHAR",
    FastFoodArray)>

If you do not specify the data type, ColdFusion examines the first fifty rows of each column to determine the data type when performing conditional expressions.

In some cases, ColdFusion can guess a data type that is inappropriate for your application. In particular, if you use columns in a WHERE clause or other conditional expression, the data types must be compatible. If they are not compatible, you must use the CAST function to recast one of the columns to a compatible data type. For more information on casting, see Using the CAST function. For more information on data type compatibility, see Understanding Query of Queries processing.

Using the CAST function

In some cases, a column's data type may not be compatible with the processing you want to do. For example, query columns returned by the cfhttp tag are all of type CF_SQL_VARCHAR, even though the contents may be numeric. In this case, you can use the Query of Queries CAST function to convert a column value into an expression of the correct data type.

The syntax for the CAST function is as follows:

CAST ( expression AS castType )

Where castType is one of the following:

• BINARY
• BIGINIT
• BIT
• DATE
• DECIMAL
• DOUBLE
• INTEGER
• TIME
• TIMESTAMP
• VARCHAR

For example:

<cfhttp url="http://quote.yahoo.com/download/quotes.csv?Symbols=csco,jnpr&format=sc1l1&ext=.csv"
    method="GET"
    name="qStockItems"
    columns="Symbol,Change,LastTradedPrice"
    textqualifier=""""
    delimiter=","
    firstrowasheaders="no">
<cfoutput>
    <cfdump var="#qStockItems#">
    <cfdump var="#qStockItems.getColumnNames()#">
</cfoutput>

<cfoutput>
<cfloop index="i" from="1" to="#arrayLen(qStockItems.getColumnNames())#">
    #qStockItems.getMetaData().getColumnTypeName(javaCast("int",i))#<br/>
</cfloop>
</cfoutput>
<cftry>
    <cfquery name="hello" dbtype="query">
        SELECT SUM(CAST(qStockItems.LastTradedPrice as INTEGER)) 
        AS SUMNOW from qStockItems
    </cfquery>
    <cfcatch>Error in Query of Queries</cfcatch>
</cftry>

<cfoutput>
    <cfdump var="#hello#">
</cfoutput>

Using aggregate functions

Aggregate functions operate on a set of data and return a single value. Use these functions for retrieving summary information from a table, as opposed to retrieving an entire table and then operating on the record set of the entire table.

Consider using aggregate functions to perform the following operations:

• To display the average of a column
• To count the number of rows for a column
• To find the earliest date in a column

Since not every relational database management system (RDBMS) supports all aggregate functions, refer to your database's documentation. The following table lists the aggregate functions that ColdFusion supports:

Syntax

aggregate_func ::= <COUNT>(* | column_name) | AVG | SUM | MIN | MAX)
([ALL | DISTINCT] numeric_exp)

Example

The following example uses the AVG() function to retrieve the average IQ of all terriers:

SELECT dog_name, AVG(dog_IQ) AS avg_IQ
FROM Dogs
WHERE breed LIKE '%Terrier';

Arbitrary expressions in aggregate functions

ColdFusion supports aggregate functions of any arbitrary expression, as follows:

SELECT lorange, count(lorange+hirange) 
FROM roysched
GROUP BY lorange;

Aggregate functions in arbitrary expressions

ColdFusion supports mathematical expressions that include aggregate functions, as follows:

SELECT MIN(lorange) + MAX(hirange)
FROM roysched
GROUP BY lorange;

Using group by and having expressions

ColdFusion supports the use of any arbitrary arithmetic expression, as long as it is referenced by an alias.

Examples

The following code is correct:

SELECT (lorange + hirange)/2 AS midrange,
COUNT(*)
FROM roysched
GROUP BY midrange;

The following code is correct:

SELECT (lorange+hirange)/2 AS x, 
COUNT(*)
FROM     roysched GROUP BY x 
HAVING x > 10000;

The following code is not supported in Query of Queries:

SELECT (lorange + hirange)/2 AS midrange,
COUNT(*)
FROM roysched
GROUP BY (lorange + hirange)/2;

Using ORDER BY clauses

ColdFusion supports the ORDER BY clause to sort. Make sure that it is the last clause in your SELECT statement. You can sort by multiple columns, by relative column position, by nonselected columns. You can specify a descending sort direction with the DESC keyword (by default, most RDBMS sorts are ascending, which makes the ASC keyword unnecessary).

Syntax

order_by_column ::= ( <IDENTIFIER> | <INTEGER_LITERAL> ) [<ASC> | <DESC>]

Example

The following example shows a simple sort using an ORDER BY clause:

SELECT acetylcholine_levels, dopamine_levels
FROM results
ORDER BY dopamine_levels

The following example shows a more complex sort; results are first sorted by ascending levels of dopamine, then by descending levels of acetylcholine. The ASC keyword is unnecessary, and is used only for legibility.

SELECT acetylcholine_levels, dopamine_levels
FROM results
ORDER BY 2 ASC, 1 DESC

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.

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>

Handling null values

ColdFusion uses Boolean logic to handle conditional expressions. Proper handling of NULL values requires the use of ternary logic. The IS [NOT] NULL clause works correctly in ColdFusion. However the following expressions do not work properly when the column breed is NULL:

WHERE (breed > 'A')
WHERE NOT (breed > 'A') 

The correct behavior should not include NULL breed columns in the result set of either expression. To avoid this limitation, you can add an explicit rule to the conditionals and rewrite them in the following forms:

WHERE breed IS NOT NULL AND (breed > 'A')
WHERE breed IS NOT NULL AND not (breed > 'A')

Concatenating strings

Query of Queries support two string concatenation operators: + and ||, as the following examples show:

LASTNAME + ', ' + FIRSTNAME
LASTNAME || ', ' || FIRSTNAME

Escaping reserved keywords

ColdFusion has a list of reserved keywords, which are typically part of the SQL language and are not normally used for names of columns or tables. To escape a reserved keyword for a column name or table name, enclose it in brackets.

Examples

ColdFusion supports the following SELECT statement examples:

SELECT [from] FROM parts;
SELECT [group].firstname FROM [group];
SELECT [group].[from] FROM [group];

ColdFusion does not support nested escapes, such as in the following example:

SELECT [[from]] FROM T;

The following table lists ColdFusion reserved keywords:

Using Queries of Queries with dates

If you create a query object with the QueryNew function and populate a column with date constants, ColdFusion stores the dates as a string inside the query object until a Query of Queries is applied to the query object. When ColdFusion applies a Query of Queries to the query object, it converts the string representations into date objects.

Query of Queries supports date constants in SQL and ODBC format, as follows:

• SQL format: Dates, times, or timestamps in one of the following format:
  • Date string: yyyy-mm-dd, for example, 1955-06-13.
  • Time string: hh:mm:ss[.[nnn]], for example, 14:34:30.75.
  • Timestamp string: yyyy-mm-dd hh:mm:ss[.[nnn]], for example, 1924-01-14 12:00:00.000.
• ODBC format: Dates, times, or timestamps in one of the following format:
  • Date string: {d 'value'}, for example, {d '2004-07-06'}.
  • Time string: {t 'value'}, for example, {t '13:45:30'}.
  • Timestamp string: {ts 'value'}, for example, {ts '2004-07-06 13:45:30'}.

If you want to convert the date to its original format, use the DateFormat function and apply the "mm/dd/yy" mask.

Understanding Query of Queries performance

Query of Queries performs very well on single-table query objects that were accessed directly from a database. This is because ColdFusion stores meta information for a query object accessed from a database.

When working with a query resulting in a SQL join, Query of Queries performs as follows:

1. Query of Queries is very efficient for simple joins in which there is only one equality between two column references or constants, for example:
   SELECT T1.a, b, c, d FROM T1, T2 WHERE T1.a = T2.a
   
   
2. Query of Queries is less efficient for joins in which the predicate contains multiple expressions, for example:

   SELECT T1.a, b, c, d FROM T1, T2 
   WHERE T1.a = T2.a AND T1.b + T1.c = T2.b + T2.c
   

   
   

Understanding Query of Queries processing

Query of Queries can process the following:

• Column comparisons
• Queries passed by reference
• Complex objects

Comparing columns with different data types

Starting with ColdFusion MX 7, ColdFusion includes enhancements that allow you to compare columns with different data types.

If one of the operands has a known column type (only constants have an unknown column type), Query of Queries tries to coerce the constant with an unknown type to the type of the operand with metadata. The pairs of allowed coercions are as follows:

• Binary, string
• Dates, string
• Numeric, bigdecimal
• Boolean, numeric

That is, ColdFusion can coerce between binary and string, but not between date and string.

If both operands have known data types, the types must be the same. The only exception is that ColdFusion can coerce among integer, float, and double.

If both operands are constants, ColdFusion tries to coerce the values, first to the most restrictive type, then to the least restrictive type.

• First to binary then to string.
• First to date then to string.
• First to boolean then to numeric.

Passing queries by reference

A Query of Queries is copied by reference from its related query; this means that ColdFusion does not create a new query when you create a Query of Queries. It also means that changes to a Query of Queries, such as ordering, modifying, and deleting data, are also applied to the base query object.

If you do not want the original query to change, use the Duplicate function to create a copy and create the Query of Queries using the copied query.

Managing complex objects

You cannot use Query Of Queries on a record set that contains complex objects, such as arrays and structures.