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.
Want to use code from this post? Check out the license.
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.