Double Sql Join Problem S’abonner
We have just moved to MS Dynamics Ax ERP. Unfortunately this ERP system has a far more complicated database schema than our old ERP system. I am having a problem with a simple International address label used for deliveries to our International customers. The Salestable has fields for the recepient, the delivery street, the delivery city, the delivery state/county, the delivery POcode and the delivery country id. We need the country name not the id code. The table that defines the country id to the country name is the AddressCountryRegion table. So all we have to do is join the salestable to the AddressCountryRegion tables on salestable.addresscountryID=AddressCountryRegionID.
The problem is that the Ax database contains multiple environments called "Companies" so nearly every table contains a field called DataareaID that defines the company the row belongs to.
So to get the data we want we need two join the two tables Salestable and AddressCountyRegion with two joins, salestable.addressCountryID=AddressCountryRegion.countryregionID and salestable.dataareaID=AddressCountryregion.dataareaid. I can produce a query in SQL SERVER Management Studio based on these two queries that obtains the data we want correctly. However when I transfer this query to Bartender 10 it complains about having two joins between the same two tables.
So I tried removing one of the joins and replacing it with two "where" causes such as "and salestable.dataareaID='TES5' and AddressCountryRegion.dataareaID='TE5'". Now it found the correct data when we did the print preview but failed to print anything when printed out the labels --- weird.
Vous devez vous connecter pour laisser un commentaire.