Here is something I stumbled across. Within the context of Query of Queries, the number 1 has the datatype bigint. Here is an example using an oracle query to start.
<cfquery name="q1" datasource="your oracle dsn">
select 1 x
from dual
</cfquery>
<cfquery name="q2" dbtype="query">
select x
from q1
<!---
union
select 1 x
from q1
--->
</cfquery>
That code will run successfully. However, if you uncomment the part that makes it a union query, you get this error message.
Query Of Queries runtime error. |
| Cannot mix types DECIMAL and BIGINT in a compare binary operation. |
In this simple example, oracle is returning a decimal and the constant 1 is the bigint. The workaround is to use the cast function when selecting the constant.
This caught me by surprise. In the place where it really happened to me, the database field was an integer datatype. If they didn't match, oh well, but bigint? That's just dumb.
