Report fails to execute. Error converting data type varchar to numeric

Problem

Report fails to execute and displays an error: message code="-28" Error converting data type varchar to numeric.

Symptom

Error: Audit.RTUsage.QOS <message code="-28" severity="error" title="QE-DEF-0459 CCLException" type="general">RQP-DEF-0177 An error occurred while performing operation &apos;sqlOpenResult&apos; status=&apos;-28&apos;. UDA-SQL-0114 The cursor supplied to the operation &quot;sqlOpenResult&quot; is inactive. UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Error converting data type varchar to numeric. (SQLSTATE=22018, SQLERRORCODE=8114)

Cause

An error occurred while performing converting operation of data type varchar to numeric.

Solution:

This error might be caused due to the use of the "cast" function, It will fail if the data item has spaces or other non numeric characters.
Possible solution:
- Try using the "trim" function within the cast so that it trims off any spaces before and after the actual numerical value prior to casting it to integer.
- Verify if the data has any illegal characters, run either "min" and "max" functions on the column to see if there are any non numeric values. This can be done using a query tool directly against the database.

www.cogknowhow.com