update_recordset using a string constant

(0) Share

Report

Posted on by Pete Alberts

3,542

Hi folks

I have been struggling with an update_recordset statement. It comes down to this:

update_recordset MyTable setting MyField = joinedTableX.Name   " "   joinedTableY.Name
    *join the tables*

SQL ignores the space.

I have tried a numerous variations (possibly all of the variations lol) and it seems that it only ignores the last space in the string constant.

update_recordset MyTable setting
    MyField = joinedTableX.Name   " - "   joinedTableY.Name

If we take an example where joinedTableX.Name = "HelloX" and joinedTableY.Name = "HelloY". Then the result for the first piece of code (content of MyTable.MyField) will be "HelloXHelloY". And the second piece of code will produce "HelloX -HelloY". Note the missing second space.

I have achieved the solution using a while select. But I would like to understand why this happens so that I can implement my solution using a set based operation.

Thanks for your attention.

I have the same question (0)

All responses (11)

Answers (3)

Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi Pete

Which version are you using? In my case, it works as it should work and all spaces are in place (but it was query without join).

BR, Sergei

Was this reply helpful? Yes No
Pete Alberts 3,542 on at

Like (0)

Report

Crisis...I should've realised this is a bug...

I'm on 10.0.6 PU30

Let me test with some other tables and on another version. Then I'll get back to you.

Was this reply helpful? Yes No
Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi

In my case, it's 10.0.4 PU28.

BR, Sergei

Was this reply helpful? Yes No
Pete Alberts 3,542 on at

Like (0)

Report
I haven't been able to find a VM that is not on PU30. Our IT/technical/VM guy has been very thorough with updating everything.

Could you be so kind to try with a join? A very simplified version of my scenario (which I am currently using to test) is MyTableX with key fields MainAccount (int64) and CompanyDataAreaId (string). Then I have field DisplayName (string) which needs to be populated. It is a cross company table.

class WhatADay { static void main(Args _args) { MyTableX myTable = MyTableX::initForSetBased(); //the method returns a buffer initialized for set based operations CompanyInfo company; MainAccount account; //here the space is ignored entirely update_recordset mytable setting DisplayName = company.DataArea " " account.MainAccountId join company where company.DataArea == myTable.CompanyDataAreaId join account where account.RecId == myTable.MainAccount; //here the second space is ignored update_recordset mytable setting DisplayName = company.DataArea " - " account.MainAccountId join company where company.DataArea == myTable.CompanyDataAreaId join account where account.RecId == myTable.MainAccount; //space is ignored update_recordset mytable setting DisplayName = account.MainAccountId " " account.Name join account where account.RecId == myTable.MainAccount; //both spaces ignored update_recordset mytable setting DisplayName = account.MainAccountId " " account.Name join account where account.RecId == myTable.MainAccount; //code does the same if I use a varialble //String space = " "; //This completes succesfully while select forupdate myTable join company where company.DataArea == myTable.CompanyDataAreaId join account where account.RecId == myTable.MainAccount { ttsbegin; myTable.DisplayName = strFmt("%1 %2",company.DataArea,account.Name); myTable.update(); ttscommit; } } }

I briefly tried to specify the space with a SysComputedColumn method. But couldn't achieve the desired result.

If you could try with a join I can be reasonably sure that the problem is version related or maybe something funny with my VM.

Was this reply helpful? Yes No
Verified answer

Sergei Minozhenko 23,093 on at

Like (1)

Report

Hi Pete

I realized that my first test was incorrect because I used a table with an update method overridden. When I disabled event and data methods and run the test again, space was dropped out. Based on the execution plan the value already comes without space as a parameter. If I get PU32, I can check it there as well, but unlikely it was fixed.

BR, Sergei

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report
Hi Pete,

did you try assigning the value to a variable and then updating the table with it?

var value = joinedTableX.Name " - " joinedTableY.Name; update_recordset MyTable setting MyField = value;

Was this reply helpful? Yes No
Verified answer

Blue Wang on at

Like (1)

Report

Hi Pete,

I remember it seems that since AX2009, trailing spaces are no longer supported, that is, spaces are removed from right to left, until the non-space characters are known.

That is what you described, when you use " - ", it will only be updated to " -", while the whole is blank, all will be removed.

-

I'm trying to find documentation for your reference.

Was this reply helpful? Yes No
Suggested answer

ThivaKar 931 on at

Like (1)

Report

Hi Pete,

You can do this by adding all the MainAccount Name in List with the required spaces.

(list.addEnd(" - "+Account.Name)

and use this list to update in updateRecordset.

No one other options to achieve this in updaterecordset.

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

Not sure, but, May be try insert_recordset to TmpRecIdMap and then update from there.

Was this reply helpful? Yes No
Pete Alberts 3,542 on at

Like (1)

Report

Hey guys, thanks for the replies.

Sergey - thanks for confessing ;) I don't think it is a bug...though I feel this is a point where "not implemented" and "bug" is quite close together. Also, a colleague of mine, who has 4 decades of experiences, in the twilight of his career and basically already in his grave, mentioned that he remembers something in the direction that this is not implemented. And then Blue also mentioned that it has always been like this.

Manish - Yes I have tried with a variable. Same result. I think I mentioned it somewhere in all the essays I wrote.

Blue - thank you very much. If you find something - please let me know.

Rusty - I have not used lists with set based operations. Could you expand a little bit more?

Bharath - I think that will be plan B, if I don't manage to achieve this with update_recordset.

Was this reply helpful? Yes No