User Guide

Query of Queries user guide 509
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.
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;
The following table lists ColdFusion reserved keywords:
ABSOLUTE ACTION ADD ALL ALLOCATE
ALTER AND ANY ARE AS
ASC ASSERTION AT AUTHORIZATION AVG
BEGIN BETWEEN BIT BIT_LENGTH BOTH
BY CASCADE CASCADED CASE CAST