Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at RIA Unleashed (Nov. 2010) with:

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.