Skip to main content
Ben Nadel at the jQuery Conference 2010 (Boston, MA) with: Richard Worth
Ben Nadel at the jQuery Conference 2010 (Boston, MA) with: Richard Worth ( @rworth )

ColdFusion Query of Queries Creates BIGINT Values When Hard Coding Integers

By
Published in Comments (6)

Just a minor thing that I came across today; ColdFusion query of queries defines hard-coded integer values as type BIGINT. While this is a very minor detail, it can cause some issues when performing a UNION as I had to do today (I had join a query to itself in a parent-child relationship query).

I had a query that looked like this:

<!--- Query for top level vendors. --->
<cfquery name="qTopLevelVendor" dbtype="query">
		(
			SELECT
				id,
				name,
				has_children
			FROM
				qVendor
			WHERE
				parent_id = 0
		)

	UNION

		(
			SELECT
				parent_id,
				parent_name,
				( 0 ) AS has_children
			FROM
				qVendor
			WHERE
				parent_id != 0
		)
</cfquery>

This threw thew following error:

Query Of Queries runtime error. Cannot mix types "INTEGER" and "BIGINT" in a "compare" binary operation.

The problem was with this line of code:

( 0 ) AS has_children

In the original query, this was an integer value. The query of queries is making that a BIGINT, which cannot be compared properly. To overcome this, I had to convert one of the values:

<!--- Query for top level vendors. --->
<cfquery name="qTopLevelVendor" dbtype="query">
		(
			SELECT
				id,
				name,
				has_children
			FROM
				qVendor
			WHERE
				parent_id = 0
		)

	UNION

		(
			SELECT
				parent_id,
				parent_name,
				CAST( 0 AS INTEGER ) AS has_children
			FROM
				qVendor
			WHERE
				parent_id != 0
		)
</cfquery>

Notice that I am casting the hard coded value as an integer. That did the trick. It's not often that I really think about the data types of hard coded values, but this just goes to show that that is very important.

Want to use code from this post? Check out the license.

Reader Comments

29 Comments

Thanks Ben! This was actually happening to me when performing a COUNT() operation in a previous query of queries. I changed it to CAST(COUNT(1) AS INTEGER) and it worked great.

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel