Data Flow Library Function Error - Cannot evaluate constant value with this type\n\tat

Question

Data Flow Library Function Error - Cannot evaluate constant value with this type\n\tat

Sujit Purkayastha Das 20

I am getting below error in Data flow library function, parseDouble:

Cannot evaluate constant value with this type\n\t

Below is the Data flow library function:

toDecimal(concat(toString(toLong(substring(i1, 1, i2))), '.', toString(toLong(substring(i1, i2 + 1, i3)))), i2 + i3, i3)

i1 is type string, i2 and i3 is of type integer

The calling Data Flow is:

parseDouble(trim(substring(value, 236, 18)), 9, 9) where i2=9 and i3=9

The function works fine if I hard code i2 and i3 in it.

toDecimal(concat(toString(toLong(substring(i1, 1, 9))), '.', toString(toLong(substring(i1, 10, 9)))), 18, 9)

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-08-12T14:40:19.41+00:00
@Sujit Purkayastha Das

Thanks for using MS Q&A platform and posting your query.

Here are a few potential issues to consider:

i1 is a string, but are you sure it's always a valid input for the substring function? If i1 is empty or has a length less than i2 or i3, it might cause issues.

toLong(substring(i1, 1, toInteger(i2))) and toLong(substring(i1, toInteger(i2) + 1, toInteger(i3))) might be causing issues if the substrings are not valid long values.

toInteger(i2) + toInteger(i3) might be causing issues if the result is not a valid integer.

To troubleshoot this further, you can try the following:

Add some error handling or logging to check the values of i1, i2, and i3 before executing the expression.

Break down the expression into smaller parts and test each part separately to identify which one is causing the issue.

Consider using try-catch blocks to catch any exceptions that might be thrown during the execution of the expression

Hope this helps. Do let us know if you any further queries.
Sujit Purkayastha Das 20 Reputation points

2024-08-12T19:19:28.74+00:00

Thanks @phemanth On further investigation, it looks the precision and scalar values are causing the issue. The flow runs without any error if the function is updated as below (without precision and scalar values).

toDecimal(

concat(

toString(toLong(substring(i1, 1, toInteger(i2)))),

'.',

toString(toLong(substring(i1, toInteger(i2) + 1, toInteger(i3))))

)

)

But when I add back the precision and scalar values, it gives same error. The input to function always remains the same. If I add back precision and scaler value in function using hard coded values instead of arguments, then it works fine.

toDecimal(

concat(

toString(toLong(substring(i1, 1, toInteger(i2)))),

'.',

toString(toLong(substring(i1, toInteger(i2) + 1, toInteger(i3))))

),

9 + 9,

9

)

Accepted answer

2 additional answers

Your answer

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-08-12T14:40:19.41+00:00

@Sujit Purkayastha Das

Thanks for using MS Q&A platform and posting your query.

Here are a few potential issues to consider:

i1 is a string, but are you sure it's always a valid input for the substring function? If i1 is empty or has a length less than i2 or i3, it might cause issues.

toLong(substring(i1, 1, toInteger(i2))) and toLong(substring(i1, toInteger(i2) + 1, toInteger(i3))) might be causing issues if the substrings are not valid long values.

toInteger(i2) + toInteger(i3) might be causing issues if the result is not a valid integer.

To troubleshoot this further, you can try the following:

Add some error handling or logging to check the values of i1, i2, and i3 before executing the expression.

Break down the expression into smaller parts and test each part separately to identify which one is causing the issue.

Consider using try-catch blocks to catch any exceptions that might be thrown during the execution of the expression

Hope this helps. Do let us know if you any further queries.
Sujit Purkayastha Das 20 Reputation points

2024-08-12T19:19:28.74+00:00

Thanks @phemanth On further investigation, it looks the precision and scalar values are causing the issue. The flow runs without any error if the function is updated as below (without precision and scalar values).

toDecimal(

concat(

toString(toLong(substring(i1, 1, toInteger(i2)))),

'.',

toString(toLong(substring(i1, toInteger(i2) + 1, toInteger(i3))))

)

)

But when I add back the precision and scalar values, it gives same error. The input to function always remains the same. If I add back precision and scaler value in function using hard coded values instead of arguments, then it works fine.

toDecimal(

concat(

toString(toLong(substring(i1, 1, toInteger(i2)))),

'.',

toString(toLong(substring(i1, toInteger(i2) + 1, toInteger(i3))))

),

9 + 9,

9

)

Answer 1

@Sujit Purkayastha Das

Thanks for using MS Q&A platform and posting your query.

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Ask: I am getting below error in Data flow library function, parseDouble:

Cannot evaluate constant value with this type\n\t

Below is the Data flow library function:

toDecimal(concat(toString(toLong(substring(i1, 1, i2))), '.', toString(toLong(substring(i1, i2 + 1, i3)))), i2 + i3, i3)

i1 is type string, i2 and i3 is of type integer

The calling Data Flow is:

parseDouble(trim(substring(value, 236, 18)), 9, 9) where i2=9 and i3=9

The function works fine if I hard code i2 and i3 in it.

toDecimal(concat(toString(toLong(substring(i1, 1, 9))), '.', toString(toLong(substring(i1, 10, 9)))), 18, 9)

**Solution:**On further investigation, it looks like the precision and scalar arguments toDecimal function is causing an issue. If I remove them then it works though the resultant number has only 2 decimal places. Below works:

toDecimal(

concat(

toString(toLong(substring(i1, 1, toInteger(i2)))),

'.',

toString(toLong(substring(i1, toInteger(i2) + 1, toInteger(i3))))

)

If I add them back (snippet below) then I get the same error.

toDecimal(

concat(

toString(toLong(substring(i1, 1, toInteger(i2)))),

'.',

toString(toLong(substring(i1, toInteger(i2) + 1, toInteger(i3))))

),

toInteger(i2) + toInteger(i3),

toInteger(i3)

)

Please note that the input to the function remains same for both.

parseDouble(trim(substring(value, 236, 18)), 9, 9)

It works when I pass constant values for precision and scalar arguments as below:

toDecimal(

concat(

toString(toLong(substring(i1, 1, toInteger(i2)))),

'.',

toString(toLong(substring(i1, toInteger(i2) + 1, toInteger(i3))))

),

9 + 9,

9

)

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Sujit Purkayastha Das 20 Reputation points

2024-08-13T14:20:37.72+00:00

The solution looks to be that we cannot pass parameters for precision and scaler values, it needs to be constant value within data library function. My use case is that the input file is fixed width file with multiple precision and scaler values for different columns. Due to this limitation, I will need to create multiple data library functions with different precision and scalar values within it, instead of using single function with those as parameters.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-08-13T16:50:55.91+00:00

@Sujit Purkayastha Das

Appreciate if you could share the feedback on our feedback channel. Which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.

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.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-08-14T11:49:22.2233333+00:00

@Sujit Purkayastha Das Following up to see if the above answer was helpful. 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.

Answer 2

Bouhmidi, Asmaa 0

Hello, you need to ensure that i2 and i3 are evaluated as constants or properly converted to the expected types before being used in the substring or other functions

try to cast the two variables as follow

toDecimal(
  concat(

    toString(toLong(substring(i1, 1, toInteger(i2)))), 

    '.', 

    toString(toLong(substring(i1, toInteger(i2) + 1, toInteger(i3))))

  ), 

  toInteger(i2) + toInteger(i3), 

  toInteger(i3)
)
```Please mark my answer as accepted if it helped

Sujit Purkayastha Das 20 Reputation points

2024-08-10T23:15:36.0766667+00:00

Thank you! I made the change for toInteger. But it is still giving me the same error:

{"message":"Job failed due to reason: Cannot evaluate constant value with this type. Details:java.lang.Throwable: Cannot evaluate constant value with this type\n\tat com.microsoft.dataflow.NodeLogicalPlan.eval(NodeLogicalPlan.scala:65)\n\tat com.microsoft.dataflow.NodeLogicalPlan.$anonfun$eval$3(NodeLogicalPlan.scala:28)\n\tat scala.collection.TraversableLike.$anonfun$map$1(TraversableLike.scala:286)\n\tat scala.collection.mutable.ResizableArray.foreach(ResizableArray.scala:62)\n\tat scala.collection.mutable.ResizableArray.foreach$(ResizableArray.scala:55)\n\tat scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:49)\n\tat scala.collection.TraversableLike.map(TraversableLike.scala:286)\n\tat scala.collection.TraversableLike.map$(TraversableLike.scala:279)\n\tat scala.collection.AbstractTraversable.map(Traversable.scala:108)\n\tat com.microsoft.dataflow.NodeLogicalPlan.eval(NodeLogicalPlan.scala:28)\n\tat com.microsoft.dataflow.NodeLogicalPlan.$anonfun$eval$2(NodeLogicalPlan.scala:21)\n\tat scala.collection.immutable.List.map(List.scala:293)\n\tat com.microsoft.dataflow.NodeLogicalPlan.eval(NodeLogicalPlan.scala:21)","failureType":"UserError","target":"TradeRevenue Parse and Store","errorCode":"DFExecutorUserError"}

Answer 3

Thanks @phemanth

On further investigation, it looks like the precision and scalar arguments toDecimal function is causing an issue. If I remove them then it works though the resultant number has only 2 decimal places. Below works:

toDecimal(

concat(

toString(toLong(substring(i1, 1, toInteger(i2)))),

'.',

toString(toLong(substring(i1, toInteger(i2) + 1, toInteger(i3))))

)

If I add them back (snippet below) then I get the same error.

toDecimal(

concat(

toString(toLong(substring(i1, 1, toInteger(i2)))),

'.',

toString(toLong(substring(i1, toInteger(i2) + 1, toInteger(i3))))

),

toInteger(i2) + toInteger(i3),

toInteger(i3)

)

Please note that the input to the function remains same for both.

parseDouble(trim(substring(value, 236, 18)), 9, 9)

It works when I pass constant values for precision and scalar arguments as below:

toDecimal(

concat(

toString(toLong(substring(i1, 1, toInteger(i2)))),

'.',

toString(toLong(substring(i1, toInteger(i2) + 1, toInteger(i3))))

),

9 + 9,

9

)

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-08-13T11:44:58.9066667+00:00

@Sujit Purkayastha Das Glad to know your issue has been resolved. Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

Share via

Data Flow Library Function Error - Cannot evaluate constant value with this type\n\tat

2 additional answers

Your answer