I ran into the above error today when trying to run a SUM() function on a query retrieved from a database using the Coldfusion Query of Queries sweetness. While the error itself is fairly explanatory I couldn’t figure out where my column being summed was being typed as a string as opposed to an integer. As such I began reading up in the Coldfusion docs on how to type data columns via a Query of Queries. It turns out that it’s very doable with a Cast() function. So while not using Cast() would throw the above error, using it to cast the column data type as an Integer resolved the issue.

Won’t work while My_Total is a type string (VARCHAR)

SELECT SUM(My_String_Column) AS My_Total
FROM Some_Table

Will work while My_Total is a type string (VARCHAR)

SELECT SUM(Cast(My_String_Column AS INTEGER)) AS My_Total
FROM Some_Table

So, fairly straight-forward usage. Any column can be cast as a number of data types on the fly within the Query of Queries. Just as a side-note, I did resolve the actual issue after discovering the Cast() function. Essentially, I typed my return variable in my Oracle Function as an NVARCHAR2. Hence my value was returning as a string. Once I changed my return data type I no longer needed the Cast() function in my Query of Queries. Nonetheless, it’s pretty cool functionality provided by Coldfusion.