ColdFusion query-of-queries, tips for adding dynamic columns

Supposing you’re doing a query of queries, and you want to add a dynamic column, perhaps based on a mixture of existing CF variables and values from the original query. There are a few gotchas to watch out for.

Firstly, you have to use single quotes ‘ ‘ instead of double quotes ” ” for anything in the string. This is contrary to ColdFusion’s normal allowance for any mixture, e.g. this is usually perfectly fine:

<cfset x = "hi" & 'hi'>

However when trying to use ” ” in the query-of-queries, it throws the error:

Query Of Queries syntax error.
Encountered “. Lexical error at line 3, column 25. Encountered: “\”" (34), after : “”

This query will throw that error:

<cfquery name="rstGetStuff" dbtype="query">
	SELECT 	"Hello World" AS columnName
	FROM rstOriginalQuery
</cfquery>

But this is fine:

<cfquery name="rstGetStuff" dbtype="query">
	SELECT 	'Hello World' AS columnName
	FROM rstOriginalQuery
</cfquery>

Secondly, if you’re wanting to refer to variables within that string, just add them into the string surrounded by # #. Again, contrary to normal CFML, where these are equivalent:

<cfset x = "hi " & variables.strName>
<cfset x = "hi #variables.strName#">

So this throws the error:

Query Of Queries syntax error.
Encountered “.. Incorrect Select Statement, Expecting a ‘FROM’, but encountered ‘.’ instead, A select statement should have a ‘FROM’ construct.

<cfset strName = "Duncan">

<cfquery name="rstGetStuff" dbtype="query">
	SELECT 	'Hello ' + strName AS columnName
	FROM rstOriginalQuery
</cfquery>

Instead you have to do

<cfquery name="rstGetStuff" dbtype="query">
	SELECT 	'Hello #strName#' AS columnName
	FROM rstOriginalQuery
</cfquery>

Thirdly, if you’re wanting to add values from the original query into that dynamic column, you might need to CAST it to the correct type. In this case I have a column in the original query, intIDColumn, that is a numeric ID.

This throws the error:

Query Of Queries runtime error.
Cannot mix types INTEGER and VARCHAR in a + binary operation.

<cfquery name="rstGetStuff" dbtype="query">
	SELECT 	'/some/url/path/' + intIDColumn + '/' AS strURL
	FROM rstOriginalQuery
</cfquery>

Instead I have to cast it to a VARCHAR when trying to add it into my string.

<cfquery name="rstGetStuff" dbtype="query">
	SELECT 	'/some/url/path/' + CAST(intIDColumn AS VARCHAR) + '/' AS strURL
	FROM rstOriginalQuery
</cfquery>

About these ads