IBM DB2 SQLException with “DB2 SQL Error: SQLCODE=-420, SQLSTATE=22018, SQLERRMC=DECFLOAT, DRIVER=3.66.46”

db2jasper-reports

I am working on a Jasper report using iReport 5.6 and IBM DB2 as data source. I am passing a list of strings as a parameter to the report and that is where the problem rises. The query is below; 

SELECT customers.mobile_number, 
    COALESCE(Count(DISTINCT transaction_entries.transaction_id), 0) AS 
    number_of_transactions, 
    COALESCE(Sum(Abs(transaction_entries.amount)) / 100, 0)         AS 
    volume_of_transactions 
FROM   transaction_entries 
    JOIN customers 
      ON customers.id = transaction_entries.customer_id 
WHERE  transaction_entries.transaction_type = 'Seasonal' 
    AND transaction_entries.notification_text <> 'Notification' 
    AND customers.mobile_number IN ( $p ! {listOfMobileNumbers} ) 
GROUP  BY customers.mobile_number

When I try to generate the report I get the error Caused by: com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-420, SQLSTATE=22018, SQLERRMC=DECFLOAT, DRIVER=3.66.46.

Any idea why ? and the possible solution ?

Best Answer

I would first verify that by commenting-out the last predicate of the WHERE clause avoids the error; i.e. redact the failing statement such that the IN predicate referencing the Jasper variable as input is no longer part of the query.
Then, determine what defines that variable replacement, from the output of the following query:
select '$p ! {listOfMobileNumbers}' from sysibm.sysdummy1
If the effect of the above query, used to reveal the data in that list, presents something like '1234,567,890', then I would suggest modifying the data that defines that list to reflect either of '1234','567','890' or 1234, 567, 890 instead.

FWiW: IMO the actual DDL [for column(s) or the TABLE] is much clearer to a reader than suggesting merely that:

The mobile_number field is returned from the database as a String and not a DECIMAL

Related Topic