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)

Sort by

Martin Dráb 239,152 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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
Copy link

Link copied!

I want to inner join with InventTrans and BODDeliverySerial tables.

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

Like (0)

Report
Copy link

Link copied!

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
Copy link

Link copied!

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 239,152 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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
Copy link

Link copied!

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 239,152 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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