Hi Everyone,
I am getting below error while updating a column value manually using SQL sandbox
"Arithmetic overflow error converting expression to data type bigint.<br/>The statement has been terminated."
1. Requirement: Need replace first 4 characters/digits from the existing column value with '1234'
2, Query used to update value:
Update {DemoTable) SET {DemoTable}.[Democolumn] = CAST(Replace({DemoTable}.[Democolumn] , LEFT({DemoTable}.[Democolumn] ,4),'1234'), As bigint)
3. Column data type: Long integer
I have tried giving data type in CAST function as "LongInteger" but it show error as invalid type
Regards,
Ajay
Hi @Ajay Shrivastava ,
OutSystems LongInteger actually translates to bigint for SQL Server. Source: https://success.outsystems.com/documentation/11/reference/outsystems_language/data/database_reference/database_data_types/
It means using bigint in your cast is actually correct.
Would you mind checking the data that you actually have in that column?
Arithmetic overflow occurs when the data that you have exceeds that actual value the data type can hold. In your case, bigint (long integer) can hold values -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Although, the only way I can see that it will overflow is when your value is negative 19 digits. So when you did the replace, it could have replaced the negative and made your new value as positive 20 digits which will throw arithmetic overflow.
Bryan
Try this query
UPDATE {DemoTable} SET {DemoTable}.[Democolumn] = CAST(LEFT('1234' + SUBSTRING(CAST({DemoTable}.[Democolumn] AS VARCHAR(20)), 5, 16), 20) AS BIGINT)
before
after
Note : The reason for using VARCHAR(20) is that it can accommodate the full possible range of BIGINT values (which can be up to 20 characters, including a negative sign).
Also this works only for +ve values in Demo Column.