Skip to main content
Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.

ColdFusion Query of Queries Creates BIGINT Values When Hard Coding Integers

By Ben Nadel on
Tags: ColdFusion

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.



Reader 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.