ColdFusion Query of Queries Creates BIGINT Values When Hard Coding Integers
Posted October 24, 2006 at 2:24 PM by Ben Nadel
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. I just fell into this exact same trap, and thankfully there you were to guide me out. Cheers
@Matt,
Awesome. It's all about team work :)
What Matt said!
Thanks Ben
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.
@Robert,
Sweeeeet!
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.
