Query Of Queries runtime error.Cannot apply the binary numeric operator [*|/] on a non numeric type

Question

I have a script that queries the database for order information and then a query of that query in order to get order totals. The code looks like this.

<!--- Retrieve order information from database --->
<CFQUERY  NAME="GetOrder">
  SELECT 
    oi.OrderQty, oi.ItemPrice
  FROM 
    Orders o,
    OrderItems oi, 
  WHERE 
    o.ID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#ATTRIBUTES.OrderID#">
    AND o.ID   = oi.OrderID
</CFQUERY>

<!--- Query the GetOrders query to find total $$ --->
<CFQUERY DBTYPE="Query" NAME="GetTotal">
  SELECT SUM(ItemPrice * OrderQty) AS OrderTotal
  FROM GetOrder
</CFQUERY>

The itemPrice and OrderQty fields in the database are both set as numeric Data Types. For some reason when I run the script I get the error, "Query Of Queries runtime error. Cannot apply the binary numeric operator [*|/] on a non numeric type"

I even ran a quick check to see if the numbers were numeric in the query and aborted the script before the query of queries. The check I ran looks like this.

<cfoutput>
<cfif isnumeric(GetOrder.OrderQty)>1<cfelse>0</cfif>
<cfif isnumeric(GetOrder.ItemPrice)>1<cfelse>0</cfif>
<cfabort>
</cfoutput>

Both results of the cfifs came back as "1"

Any help with this would be greatly appreciated.

Answer 1

Forget the Query of Queries and cfloop your query to do the same thing, preferably using precisionEvaluate() to avoid floating point rounding error.

<cfset orderTotal = 0>
<cfloop query="GetOrder">
    <cfset orderTotal = precisionEvaluate(orderTotal + (ItemPrice * OrderQty))>
</cfloop>

Why it didn't work could be because of a number of things. Maybe your DB driver did not return the correct sql type.

The isnumeric code only tests if the string is numeric, not the internal representation of that string in the query object.