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 the jQuery Conference 2010 (Boston, MA) 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.


Looking For A New Job?

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

Reader Comments

I know this is an old post but man did this stump me for a bit. Google searched the error message and your post was #1. Thanks.

Reply to this Comment

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.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.