Incorrect Input for Big Decimal

[daniellesacks]

Hello,

For my job, I copy an existing Excel file. I then compare that copy via tMap to the SQL input. My job then adds any new records to the original Excel file that is copied and also sends an email reporting the new records.

All the data comes through the SQL query as String, but I convert it to a Big Decimal in tMap.

The job runs all the way through correctly and finishes, but I always receive the following error:

Incorrect input "COUNT_ERROR" for BigDecimal.
[ERROR]: new_r2_qerror.qerror_instance_2_0.QERROR_INSTANCE - tFileInputExcel_1 - Incorrect input "COUNT_ERROR" for BigDecimal

Please see attached pictures.

What do I need to do to avoid this error?

[Jesperrekuh]

Could you please provide is with more details please provide some data example / exact values.

Excel might be effected by language settings, so in the Netherlands a 12.48 is written as 12,48 if you try to convert it it will fail and raise an error.
We need some extra log information.

Why on earth using a BigDecimal for a COUNT_ERROR... is it because of using a COUNT_BIG() function.
BigDecimal sounds a bit like overkill.

[daniellesacks]

The SQL Query is as follows:

"SELECT TO_CHAR (MAX (CREATED_ON), 'YYYY-MM-DD') AS MAX_CREATED_ON,
COUNT (*) AS COUNT_ERROR,
CREATED_BY,
ERROR_STACK,
SYSTEM_ERROR_MESSAGE,
MESSAGE
FROM Q$****_II***
WHERE CREATED_ON > SYSTIMESTAMP - INTERVAL '24000' MINUTE
AND (CREATED_BY, ERROR_STACK, SYSTEM_ERROR_MESSAGE) NOT IN
(SELECT CREATED_BY, ERROR_STACK, SYSTEM_ERROR_MESSAGE
FROM Q$****_IN****
WHERE CREATED_ON <= SYSTIMESTAMP - INTERVAL '24000' MINUTE)
GROUP BY CREATED_BY,
ERROR_STACK,
SYSTEM_ERROR_MESSAGE,
MESSAGE
ORDER BY TO_CHAR (MAX (CREATED_ON), 'YYYY-MM-DD HH24:MIS') DESC,
COUNT (*) DESC,
CREATED_BY,
ERROR_STACK,
SYSTEM_ERROR_MESSAGE,
MESSAGE "

Sample Data:

MAX_CREATED_ON	COUNT_ERROR	ERROR_STACK	MESSAGE	SYSTEM_ERROR_MESSAGE	CREATED_BY
2018-08-31	2096	ORA-06512: at "K******.P****", line 2724	p**_M****.s**_ca**_s*****_h****(): Caught an unexpected exception. SQLERRM: ORA-02291: integrity constraint (KP****_ST***.FK_C**_C**_S***_H***_S**_L**) violated - parent key not found	ORA-02291: integrity constraint (K****_S****.F*_C**_C**_S***_H****_S**_L**) violated - parent key not found	KP***_ST***

The count is a count of how many errors of a particular type are occurring. Our database generally has numbers as BigDecimals/Number types. I also tried having it read as an Integer and it gave me this message:

For input string: "COUNT_ERROR"
[ERROR]: new_r2_qerror.qerror_instance_2_0.QERROR_INSTANCE - tFileInputExcel_1 - For input string: "COUNT_ERROR"

Talend keeps believing that the count is a String even if I change the data type in the schema in the Oracle Input to Integer or Big Decimal. 

I am not sure what you mean by log information.

[Jesperrekuh]

In Talend you can easily change to INT in the schema, however your database is not the problem... excel is.
What is the real value in excel, and I do not mean the displayed value, place a tLogRow between your lookup excel and your tMap...

Did your flow work without excel loopup? no bigdecimal error stuff?
check the value for dots and commas.
In the advance settings tab of the Excel there;s a checkbox regarding 'Advanced seperator'.
And a checkbox read real values for numbers

[Anonymous]

Hi,

Facing same issue. Is that ur issue solved. Can u post the solution?

Thanks!

[daniellesacks]

The issue with Excel ended up being missing jar files. I had to work with the help desk to identify and add the correct ones.

[ShastriV]

Do you know which jar file was missing? I am facing the same issue.