Search code examples
coldfusioncoldfusion-2018qoq

Unexpected error using the sum function in a QoQ

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.

Solution

  • 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>.