Query of Queries user guide

Query of Queries supports using dot notation in table names.

SELECT tape_ID, length 
FROM A.B.Products;

A join operation uses a single SELECT statement to return a result set from multiple, related tables, typically those tables with a primary key - foreign key relationship. The two SQL clauses that perform joins are:

• WHERE clause: Query of Queries supports joins through a WHERE clause.

• INNER JOIN and OUTER JOIN: Query of Queries does not support joins through INNER JOIN or OUTER JOIN clauses.

The UNION operator lets you combine the results of two or more SELECT expressions into a single recordset. The original tables must have the same number of columns, and corresponding columns must be UNION-compatible data types. Columns are UNION-compatible data types if they meet one of the following conditions:

• The same data type; for example, both Tinyint

• Both Numeric; for example, Tinyint, Smallint, Integer, Bigint, Double, Float, Real, Decimal, or Numeric

• Both Characters; for example, Char, Varchar, or LongVarchar

• Both Dates; for example, Time, TimeStamp, or Date

select_expression = select_expression UNION [ALL] select_expression

SELECT * FROM Table1  
UNION  
SELECT * FROM Table2 

Query of Queries lets you use the following conditional operators in your SQL statements:

A Query of Queries requires that every column has metadata that defines the data type of the column. 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.

In some cases, the data type of a column is not 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 if the contents are numeric. In this case, 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>

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 recordset 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 the documentation of your database. The following table lists the aggregate functions that Query of Queries supports:

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

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

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

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

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

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

Query of Queries 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).

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

SELECT acetylcholine_levels, dopamine_levels 
FROM results 
ORDER BY dopamine_levels

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

Query of Queries supports the use of database column aliases. An alias is an alternate name for a database field or value. Query of Queries 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.

Query of Queries 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 Query of Queries. 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, 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')

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

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

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.

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

SELECT [[from]] FROM T;

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.

Query of Queries performs well on single-table query objects that were accessed directly from a database. This happens 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 efficient for simple joins in which only one equality exists 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

Query of Queries can process the following:

• Column comparisons

• Queries passed by reference

• Complex objects