Execute SQL Query: Value was either too large or too small for a Decimal

[lydia.nathan]

I tried to run one query using execute query activity, connection also successful only. but, getting this error (Value was either too large or too small for a Decimal.)

Can you help me about this ?

Thank you.

[pikorpa (Piotr Kołakowski)]

Hey @lydia.nathan,
The error “Value was either too large or too small for a Decimal” while executing a SQL query, typically occurs when the data being retrieved or manipulated in the SQL operation is outside the range that can be represented by a Decimal data type in .NET.

1. Check Data Types in SQL: Look at your SQL database and the specific query you’re executing. Identify columns in the SQL query that are of types like BIGINT, NUMERIC, or DECIMAL, which might contain very large or small values.
2. Examine Data: Check if there are any exceptionally high or low values in your data that might be causing this error.

[lydia.nathan]

@pikorpa i could see the datatype of the column is(number(31,0)) in database. The same query which is executing successfully in Dbeaver, but in UiPath Iam getting the issue. so what could be the solution any idea. thanks

[pikorpa (Piotr Kołakowski)]

I think the issue likely stems from a difference in how UiPath and DBeaver handle large numeric values. In .NET, which UiPath uses, the Decimal type has a smaller range compared to the NUMBER(31,0).

Try to modify your SQL query to cast the large numeric values to a type that is within the range of .NET’s Decimal type. For instance, you could cast it to float :
SELECT CAST(YourLargeNumberColumn as FLOAT) as YourLargeNumberColumn FROM YourTable

[lydia.nathan]

Hi,

Thanks for the solution. its working
In my case, i just casting it as Varchar and its working fine.

[pikorpa (Piotr Kołakowski)]

@lydia.nathan
Cool. Please mark my post as solution if it was helpful for you

Cheers