toDecimal function in dataflow of ADF/Azure Synapse Analytics not working correctly

Peruka, Sikander reddy 110

I have input string field with name COST with below values.

COST

2753000

217400

500

252567552

I am using toDecimal function in derived Column transformation in dataflow as below.

z_COST= toDecimal(trim(COST))

Output is as below

z_COST

27530.00

2174.00

500.00

NULL

Why is the toDecimal('252567552') returned as NULL?

How can this be returned as 2525675.52 instead?

Marcin Wasiak 0 Reputation points

2025-02-20T16:01:22.2633333+00:00

Anwsering your second question: try using Divide by 100 method.
Peruka, Sikander reddy 110 Reputation points

2025-02-20T16:21:59.76+00:00

Since toDecimal('252567552') returns NULL, so what's the maximum number toDecimal() function doesn't return NULL
Peruka, Sikander reddy 110 Reputation points

2025-02-21T17:17:40.0066667+00:00

These source input data is in string. I mentioned them as numbers so as to check if the large numbers are causing issue.

After using the provided solution:

iif(length(COST) > 8, toDecimal(toString(toLong(COST) / 100), 18, 2), toDecimal(COST, 18, 2))

Here is the output.

The first 4 rows are returning correctly. The last 2 rows are incorrect.

The correct values should be

252567552.00

123456789123.00

I want the output as below

2753000.00

217455.00

33468.00

500.00

252567552.00

123456789123.00

1 answer

Smaran Thoomu 20,305 Reputation points Microsoft Vendor

2025-02-20T16:46:33.8233333+00:00
Hi @Peruka, Sikander reddy
Welcome to Microsoft Q&A platform.

Thank you for reaching out and providing detailed information about the issue you're encountering with the toDecimal function in Azure Data Factory (ADF) or Azure Synapse Analytics.

Understanding the Issue:

When converting the string '252567552' using toDecimal(trim(COST)), the function returns NULL instead of the expected 2525675.52. This discrepancy arises because, by default, the toDecimal function in ADF assumes a precision of 10 and a scale of 2 (decimal(10,2)). In this context:

Precision (10): Total number of digits allowed, both before and after the decimal point.

Scale (2): Number of digits allowed after the decimal point.

The number '252567552' exceeds the default precision, leading to a NULL result upon conversion.

Proposed solution:

To correctly format your numbers while ensuring that only necessary values are adjusted, you can use a conditional expression in your Derived Column transformation:

iif(length(COST) > 8, toDecimal(toInteger(COST) / 100, 12, 2), toDecimal(COST, 12, 2))

For numbers longer than 8 digits, it assumes the last two digits should be after the decimal and divides by 100.

For shorter numbers, it keeps them unchanged.

Expected Output:

This should resolve the issue while keeping other numbers accurate. Let me know if you need further clarification!

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.

1 person found this answer helpful.
Peruka, Sikander reddy 110 Reputation points

2025-02-20T18:05:56.3733333+00:00

Thanks!

Peruka, Sikander reddy 110 Reputation points

2025-02-20T21:36:38.93+00:00

I have tested with these input values and the provided solution doesn't work.

COST

2753000

217455

33468

500

252567552

123456789123

iif(length(COST) > 8, toDecimal(toInteger(COST) / 100, 12, 2), toDecimal(COST, 12, 2))

It results in NULL for 123456789123 value as shown in below screenshot.

Also, toInteger of large digit isn't working and results in NULL.

Also, toLong of large digit divided by 100 results in 1.23456789123E9

toLong('123456789123') =1.23456789123E9.

Below is the screenshot.

Smaran Thoomu 20,305 Reputation points Microsoft Vendor

2025-02-21T11:53:26.7+00:00

Hi @Peruka, Sikander reddy
Thanks for testing the solution and sharing your feedback!

The initial solution was designed based on your original requirement, where the COST column was provided as a string. It successfully handled values within the standard precision limits.

However, since you are now testing with a 13-digit number (123456789123), it behaves differently. This is because larger numbers require a different approach for conversion, as functions like toInteger() and toDecimal() have precision limitations.

Updated Solution

To correctly process large numbers while maintaining accuracy, I have modified the expression:

iif(length(COST) > 8, toDecimal(toString(toLong(COST) / 100), 18, 2), toDecimal(COST, 18, 2))

Handling Large Numbers:

Instead of toInteger(), we use toLong(), which can handle larger values.

Converting to a string before applying toDecimal() ensures precision is maintained.

Dividing by 100 for Correct Formatting:

For numbers longer than 8 digits, this method moves the last two digits after the decimal.

For shorter numbers, it retains the original formatting.

Please test this updated approach and let me know if it gives the expected results for your dataset.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Peruka, Sikander reddy 110 Reputation points

2025-02-21T17:35:43.7933333+00:00

These source input data is in string. I mentioned them as numbers so as to check if the large numbers are causing issue.

After using the provided solution, here is the output

iif(length(COST) > 8, toDecimal(toString(toLong(COST) / 100), 18, 2), toDecimal(COST, 18, 2))

Output:

The first 4 rows are returning correctly. The last 2 rows are incorrect.

The correct values should be

252567552.00

123456789123.00

2753000.00

217455.00

33468.00

500.00

252567552.00

123456789123.00

Smaran Thoomu 20,305 Reputation points Microsoft Vendor

2025-02-21T17:56:35.27+00:00

Hi @Peruka, Sikander reddy

Thanks for your patience and for testing the updated solution.
Apologies, I now better understand the requirement - you want to retain the full number as a decimal without any scaling or division while ensuring large values are correctly converted.

The previous approach introduced a division operation (/100), which was intended for formatting but is unnecessary in your case. Additionally, precision limitations in toInteger() and toLong() can lead to NULL values for extremely large numbers.

Updated Solution:

To ensure all numbers, including large ones, are correctly converted to decimal without losing precision, use:

toDecimal(COST, 18, 2)

toDecimal(COST, 18, 2) ensures that all values are directly converted while allowing up to 18 digits with 2 decimal places (which will show .00 for whole numbers).

No division is applied, so numbers retain their original values.

Works correctly for large values like 252567552 and 123456789123.

This should now match your expected results. Please test this and let me know if it resolves your issue.

Hope this helps! Let me know if you have any further questions.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

toDecimal function in dataflow of ADF/Azure Synapse Analytics not working correctly

1 answer

Your answer