Duncan's blog

November 10, 2011

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

Filed under: Coldfusion — duncan @ 5:35 pm
Tags: , , ,

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>

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: