Adobe 38043740 Developing Applications - Page 450

DEVELOPING COLDFUSION 9 APPLICATIONS 445 Accessing and Using Data Note: Query of Queries does not support aliases for table names. SELECT FirstName + ' ' + LastName AS fullname from Employee; The following examples rely on these two master queries: SELECT * FROM employee SELECT * FROM roysched ORDER BY example SELECT (job_id || job_lvl)/2 AS job_value FROM employee ORDER BY job_value GROUP BY example SELECT lorange || hirange AS x, count(hirange) FROM roysched GROUP BY x HAVING example SELECT (lorange || hirange)/2 AS x, COUNT(*) FROM roysched GROUP BY x HAVING x > 10000 Handling null values 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') Concatenating strings Query of Queries support two string concatenation operators: + and ||, as the following examples show: LASTNAME + ', ' + FIRSTNAME LASTNAME || ', ' || FIRSTNAME Last updated 1/20/2012