ColdFusion Query of Queries Creates BIGINT Values When Hard Coding Integers

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.