1

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 ?

Alex K
  • 22,315
  • 19
  • 108
  • 236
Yassem
  • 21
  • 1
  • 1
  • 3
  • 1
    According to https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/codes/src/tpc/n420.html your error is `THE VALUE OF A STRING ARGUMENT WAS NOT ACCEPTABLE TO THE function-name FUNCTION`, if someone asks about the error message. – UeliDeSchwert Aug 12 '16 at 08:31
  • I have already seen that page and did not get detailed information. The mobile_number field is returned from the database as a String and not a DECIMAL. – Yassem Aug 12 '16 at 08:49
  • Maybe `mobile_number` is a non-string field and you are passing strings? – tobi6 Aug 12 '16 at 10:26
  • Check that you have same type, then I would do something like this http://stackoverflow.com/questions/38746242/use-string-parameter-for-regex-in-query/38754322#38754322 – Petter Friberg Aug 12 '16 at 11:36
  • I suspect that, while you think that you are "passing a list of strings", what DB2 sees is a single string value that cannot be converted to a number (the error message indicates that it attempts an implicit conversion to `DECFLOAT`). – mustaccio Aug 12 '16 at 20:09

1 Answers1

2

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

CRPence
  • 1,259
  • 7
  • 12