Finance | Project Operations, Human Resources, ...

temp table and strReplace

(0) Share

Report

Posted on by dynamicscon

Hi,

I had a small question.

Could there be something like the following in the query I use for tmpTable?

ECompanyOrderNo is guid field but CompanyOrderNo is str

join arcBodDeliverySerial

where strReplace(strReplace(guid2Str(inventTrans.ECompanyOrderNo),"{",""),"}","") == BodDeliverySerial.CompanyOrderNo

I have the same question (0)

All responses (7)

Answers (0)

Martin Dráb 237,904 Most Valuable Professional on at

Like (0)

Report

What is the role of InventTrans and BodDeliverySerial tables in the query? Do you have both of them in the query and you're definign a join condition? Or are you querying InventTrans records and filtr them by a value coming from outside the query?

Also, you mentioned a temporary table. Where are you using it?

Was this reply helpful? Yes No
dynamicscon 80 on at

Like (0)

Report

I want to inner join with InventTrans and BODDeliverySerial tables.

Was this reply helpful? Yes No
Martin Dráb 237,904 Most Valuable Professional on at

Like (0)

Report

Okay, and where is the temporary table? Is BODDeliverySerial temporary, or what do you mean?

Was this reply helpful? Yes No
dynamicscon 80 on at

Like (1)

Report

Code like this:

while select salesLine

join inventTrans

where salesLine.InventDimId == inventTrans.InventDimId

join BodDeliverySerial

where guid2Str(inventTrans.ECompanyOrderNo) == BodDeliverySerial.CompanyOrderNo

{

tmpTable.clear();

tmpTable.EOrderDate = salesLine.EOrderDate;

tmpTable.ECustName = salesLine.ECustName;

tmpTable.CustAccount = salesLine.CustAccount;

tmpTable.inventSerialId = inventDim.inventSerialId;

tmpTable.ItemNameDisplay = tmpTable.ItemNameDisplay;

tmpTable.ItemId = salesLine.ItemId;

tmpTable.SalesId = salesLine.SalesId;

tmpTable.insert();

}

Was this reply helpful? Yes No
Martin Dráb 237,904 Most Valuable Professional on at

Like (0)

Report

Okay, so the temporary table isn't really relevent to the problem.

When you have problem to query the data, it usually means that your data model is wrong. If you have two fields that contain logically identical values, the fact that they don't have the same data type looks wrong to me. Isn't fixing this problem the right approach?

Was this reply helpful? Yes No
dynamicscon 80 on at

Like (0)

Report

Yes Martin! I already said so. I mentioned it in my first post. I think there is a problem with the conversion there.

The system before me was set up this way and I don't need to make any changes so as not to affect other forms.

I'm trying to find another way and thought of things like guid2str but it didn't work.

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,904 Most Valuable Professional on at

Like (0)

Report

There is no translation of X++ functions to T-SQL code, which is what is sent to database.

The right approach would be fixing the bug in the data model. If you can't do that, you'll need to come up with a workaround. For example, you can create a view for one of those tables and add a computed column, which would convert the value to the right data type. I would create a view for BodDeliverySerial, because it'll likely have less records than InventTrans.

Then you'll join inventTrans.ECompanyOrderNo with the computed column of the view.

Was this reply helpful? Yes No

Join the conversation

Community site session details

temp table and strReplace

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

temp table and strReplace

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates