MACROMEDIA COLDFUSION MX 61-DEVELOPING COLDFUSION MX, Develop Manual

Page: 471 / 860

Query of Queries user guide 471
HAVING example
<cfquery name="having" dbtype="query">
SELECT (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')
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.
Caution: Earlier versions of ColdFusion let you use some reserved keywords without escaping them.
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;