Skip to main content
Solved

Possible error in data conversion using expression

T
Apprentice

Hello all,

In our program we discovered that a column should have been mandatory but was not selected to be in the SF. This has been fixed in a later version by simply making the column mandatory, and we wanted to try to use the Data conversion functionality to fill in the missing data. 

The following expression has to be used for filling the column, and this has been selected in the SF as well;

isnull(t1.calculatie_soort,(select ws.standaard_calculatie_soort_id from waarde_standaard ws))

However, when generating the code for this upgrade, we found out that the SF generates a faulty statement when inserting the data from the renamed table;

       t1."product_type" as "product_type",
       t1."calculatie_soort"(isnull(t1.calculatie_soort,(select ws.standaard_calculatie_soort_id from waarde_standaard ws))) as "calculatie_soort",
       t1."per_1_of_100" as "per_1_of_100",

As you see, unchanged columns are fine, but our change column "calculatie_soort” now causes an error, as bot the "normal” code (t1.”calculatie_soort”) and our expression are present.

Are we making an error when using the expression or is this a bug in the SF?

Best answer by Jeroen van den Belt

Hi @Thomas Ofman,

We looked into it, and the situation was a bit more nuanced than as described above.

The settings are not mutually exclusive, so it should be allowed for them to be filled at the same time. However, the implementation on how to deal with this situation is incorrect. This will be solved in the 2023.1 version of the Thinkwise Platform.

Some extra explanation:

It was already the case that when both the 'From column' and the regular default value are filled, the default value will only be applied when the value in the 'From column' is empty:

coalesce(from column, default value)

This concept was not applied when queries are used as a default value, resulting in the error during the upgrade. This has been implemented, so the coalesce is now also used when the ‘From column’ and ‘Default value query' are filled: 

coalesce(from column, default value_query)

Or in your case:

coalesce(t1."number_name", (isnull(t1.calculatie_soort,(select ws.standaard_calculatie_soort_id from waarde_standaard ws))))

 

View original
Did this topic help you find an answer to your question?
This topic has been closed for comments

Mark Jongeling
Administrator
Forum|alt.badge.img+23

Hi Thomas,

This is not supposed to happen as this will cause an error. Could you report this inside TCP? This way our Metamodel team can correct this and keep you up-to-date.

I was able to pinpoint the piece of code resulting in this problem. This will help resolving the issue quicker.

Thanks!

"Wake me up before you low-code"
T
Apprentice
Mark Jongeling wrote:

Hi Thomas,

This is not supposed to happen as this will cause an error. Could you report this inside TCP? This way our Metamodel team can correct this and keep you up-to-date.

I was able to pinpoint the piece of code resulting in this problem. This will help resolving the issue quicker.

Thanks!

Thanks Mark, I have submitted it to TCP (85416)

Mark Jongeling
Administrator
Forum|alt.badge.img+23

Looking better at it, the From column should not be filled as you are assigning a different data-source; the given Default value query. That will fix the issue 😄 

Data conversion
Upgrade script

 

"Wake me up before you low-code"
T
Apprentice
Mark Jongeling wrote:

Looking better at it, the From column should not be filled as you are assigning a different data-source; the given Default value query. That will fix the issue 😄 

Ah, I did not know that those things were mutually exclusive, thank you, this was the solution!

Perhaps it is possible to warn users that this is mutually exclusive?

Jeroen van den Belt
Administrator
Forum|alt.badge.img+8

Hi @Thomas Ofman,

We looked into it, and the situation was a bit more nuanced than as described above.

The settings are not mutually exclusive, so it should be allowed for them to be filled at the same time. However, the implementation on how to deal with this situation is incorrect. This will be solved in the 2023.1 version of the Thinkwise Platform.

Some extra explanation:

It was already the case that when both the 'From column' and the regular default value are filled, the default value will only be applied when the value in the 'From column' is empty:

coalesce(from column, default value)

This concept was not applied when queries are used as a default value, resulting in the error during the upgrade. This has been implemented, so the coalesce is now also used when the ‘From column’ and ‘Default value query' are filled: 

coalesce(from column, default value_query)

Or in your case:

coalesce(t1."number_name", (isnull(t1.calculatie_soort,(select ws.standaard_calculatie_soort_id from waarde_standaard ws))))

 

Mark Jongeling
T
2 people like this
  • Mark Jongeling
    Mark Jongeling
  • T
    Thomas Ofman
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings