Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Stack overflow error in 'SQL Component'

Hi,

I have a singe SQL query which left joins 14 tables together. It outputs result in 26 columns.
When I run the job, SQL Component which contains this query produces this error.

ERROR: Integer data overflow (multiplication)
Detail:
-----------------------------------------------
error: Integer data overflow (multiplication)
code: 1058
context:
query: 652322
location: numeric.hpp:326
process: query0_39 [pid=1796]
-----------------------------------------------

The same query gives a valid output when executed upon my local database.

It would be great if someone can help me figure this out.

Thanks and kind regards.

3 Community Answers

Matillion Agent  

Laura Malins —

Hi Thedath

It sounds like a data type issue – can you cast the affected column to be a bigint? If this doesn’t work can you provide us with an export of the affected job so we can investigate please?

Thanks
Laura


Thedath Oudarya —

Hi Laura,

Sorry for the late reply. I did few changes on the job.
I found that this error occurs because of a multiplication upon a integer column of one of the tables. This particular column stores time in seconds. I'm updating that column by multiplying by 1000, to get the values in milliseconds. I'm using 'Calculator' and 'Table Output' transformation components to do the update. When I run the job, Calculator component gives me the error. Could it be the insufficient size of the column, which occurs this error?

Thanks,
Thedath


Thedath Oudarya —

Hi Laura,

I followed your instruction, the type casting, and now its working! Thanks for the big help.

This is how I did it.

...
(cast (extract(epoch from ec.timestamp) as bigint)) * 1000 as time_milliseconds
...

Post Your Community Answer

To add an answer please login