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.
Thanks Ben. I just fell into this exact same trap, and thankfully there you were to guide me out. Cheers
Awesome. It's all about team work :)
What Matt said!
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.
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.