Skip to main content

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?

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!

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)

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

 

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?

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))))

 

Terms & ConditionsCookie settings