MACROMEDIA COLDFUSION 5-DEVELOPING, Develop Manual

Page: 55 / 438

Using Query Results in Queries 35
You can also do the following tasks:
•
Use the results of one or two queries in your SQL statement.
•
Generate computed columns.
Performing a query on a query
To generate a query using an existing query:
•
Specify the
cfquery tag’s dbtype
attribute as "
query".
•
Do not specify a
datasource
attribute.
•
Specify the names of one or more existing queries as the table names in a SQL
SELECT statement.
•
If the database content does not change rapidly, it is a good idea to use the
cachedwithin attribute to cache the query results of between page requests. This
way, ColdFusion accesses the database on the first page request, and does not
query the database again until the specified time expires. Note that you must use
the
CreateTimeSpan
function to specify the
cachedwithin
attribute value.
Note
You cannot add a literal value as a column to the SELECT list in a query of queries.
Your query generates a new query results set, identified by the value of the
name
attribute. The following example illustrates the use of a master query and a single
detail query that extracts information from the master. A more extended example
would use multiple detail queries to get different information from the same master
query.
Boolean predicates:
LIKE
NOT LIKE
IN
NOT IN
BETWEEN
NOT BETWEEN
AND
OR
Aggregate functions:
Count([DISTINCT][*] expr)
Sum([DISTINCT] expr)
Avg([DISTINCT] expr)
Max(expr)
Min(expr)
You cannot nest aggregate
functions.
Comparison operators:
<=
>=
=
<
>
<>