Unexpected error using the sum function in a QoQ

Question

While attempting to run the following code

<cfquery name="Lev1CatTotal" dbtype="query">
    SELECT 
        SUM(AMOUNT) AS TOTAL
    FROM 
        ChartData
</cfquery>

This is the error message that's generated:

Query Of Queries runtime error. The aggregate function [SUM(expression)] cannot operate on an operand of type [JAVA_OBJECT]

This code works fine when aggregating smaller amounts. However, these are the amount in the table I'm aggregating. This particular query sums to over $5.7B.

AMOUNT	FISCAL_YR	GOV_LEVEL1_CAT
979241575.14	2019	Charges for Services
97218277.18	2019	Charges to Other Governments
233197655.52	2019	Federal Aid
329567996.81	2019	Other Local Revenues
86957092.75	2019	Other Non-Property Taxes
158997846.75	2019	Other Real Property Tax Items
371012673.89	2019	Other Sources
346575244.01	2019	Proceeds of Debt
1145011131.99	2019	Real Property Taxes and Assessments
945308275.55	2019	Sales and Use Tax
921087680.04	2019	State Aid
107357596.20	2019	Use and Sale of Property

Just to move forward, as a workaround, I recoded this as follows:

<cfset TOTAL = 0>
<cfloop query="ChartData">
    <cfset TOTAL = precisionEvaluate(TOTAL + AMOUNT)>
</cfloop>

Using precisionEvaluate(), it casts the TOTAL to BigDecimal precision and avoids the error. Does someone know of a QoQ solution using the sum() function to cast this to a big decimal and avoid using this workaround? Thanks.

Answer 1

Big thanks to @BernhardDöbler for getting me to look into where ChartData comes from. Since this was inherited code, I had to look into this. It turned out that ChartData was created with the following line of code.

<cfset ChartData = QueryNew("FISCAL_YR, GOV_LEVEL1_CAT, AMOUNT")>

I noticed, the original coder didn't specify any data types for his QueryNew() statement, so I modified the line of code to

<cfset ChartData = QueryNew("FISCAL_YR, GOV_LEVEL1_CAT, AMOUNT", "VarChar, VarChar, Double")>

Once I added the Double data type to the AMOUNT column, it corrected the error when I restored back to the original code of

<cfquery name="Lev1CatTotal" dbtype="query">
    SELECT 
        SUM(AMOUNT) AS TOTAL
    FROM 
        ChartData
</cfquery>

and I was able to remove my workaround code which performed the aggregation using a <cfloop>.