Double Sql Join Problem

Legacy Poster

• 25. Mai 2012 22:20

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.

0

• 

  Legacy Poster

  ★ BarTender Hero ★

  • 3. Dezember 2012 16:31
  I have a similar problem. I need to join to a table based on 2 different fields. Bartender only allows a join on 1 field. I can get around this by using a custom SQL query. When I do this, the "browse" finds the correct data, but the print preview says none of the fields exist.
  
  ANyone have a solution? I need to do the following:
  
  SELECT FROM Table x INNER JOIN table y ON x.f1 = y.f1 and x.f2 = y.f2

  0
• 

  Legacy Poster

  ★ BarTender Hero ★

  • 21. Juli 2014 22:32

  Probably obvious. Hide the complexity within a view so Bar Tender sees only a simple table. For AX you might want a separate "linked" database keeping AX clean.

  0
• 

  Domingo Rodriguez

  Moderator

  ★ BarTender Hero ★

  • 28. Juli 2014 09:06

  To clarify: what BarTender doesn't currently support is doing a multi-database join by entering a circular table reference condition (where 1 field of a certain table appears in 2 join conditions).

   

  This is currently a feature request item.

  0
• 

  Legacy Poster

  ★ BarTender Hero ★

  • 9. November 2015 20:45
  
  

  "To clarify: what BarTender doesn't currently support is doing a multi-database join by entering a circular table reference condition (where 1 field of a certain table appears in 2 join conditions).

   

  This is currently a feature request item."

   

   

  Has this feature been added? This is a serious limitation to using bartender with SQL... Without this feature bartender useless to me, because every table in my database requires the Company field to be joined between two tables. Also many of our records use 3 tables, and like this example below OrderNum has to be used in two joins in order to retrieve Order records. It's not just using the same field on the same table twice... you can't even use the same two tables twice in two different joins with two different fields.

   

  I HAVE TO BE ABLE TO REPLICATE THIS BELOW IN BARTENDER:

   

  select  * 

  from Erp.OrderHed as OrderHed

  inner join Erp.OrderDtl as OrderDtl on 

      OrderHed.Company = OrderDtl.Company

  And

      OrderHed.OrderNum = OrderDtl.OrderNum

   

  inner join Erp.OrderRel as OrderRel on 

      OrderDtl.Company = OrderRel.Company

  And

      OrderDtl.OrderNum = OrderRel.OrderNum

  And

      OrderDtl.OrderLine = OrderRel.OrderLine

  0
• 

  Legacy Poster

  ★ BarTender Hero ★

  • 13. November 2015 19:25

  "To clarify: what BarTender doesn't currently support is doing a multi-database join by entering a circular table reference condition (where 1 field of a certain table appears in 2 join conditions).

   

  This is currently a feature request item."

   

   

  Has this feature been added? This is a serious limitation to using bartender with SQL... Without this feature bartender useless to me, because every table in my database requires the Company field to be joined between two tables. Also many of our records use 3 tables, and like this example below OrderNum has to be used in two joins in order to retrieve Order records. It's not just using the same field on the same table twice... you can't even use the same two tables twice in two different joins with two different fields.

   

  I HAVE TO BE ABLE TO REPLICATE THIS BELOW IN BARTENDER:

   

  select  * 

  from Erp.OrderHed as OrderHed

  inner join Erp.OrderDtl as OrderDtl on 

      OrderHed.Company = OrderDtl.Company

  And

      OrderHed.OrderNum = OrderDtl.OrderNum

   

  inner join Erp.OrderRel as OrderRel on 

      OrderDtl.Company = OrderRel.Company

  And

      OrderDtl.OrderNum = OrderRel.OrderNum

  And

      OrderDtl.OrderLine = OrderRel.OrderLine

   

  I called Bartender support and they suggested I use a SQL View to accomplish this. I created this View below in my SQL database and it worked great with Bartender... I actually think this works much better than creating a query within Bartender.

   

  create view camLabelsByOrder

  as

  select 

      [OrderRel].[OrderNum] as [OrderRel_OrderNum],

      [OrderRel].[OrderLine] as [OrderRel_OrderLine],

      [OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],

      [Customer].[CustID] as [Customer_CustID],

      [Customer].[Name] as [Customer_Name],

      [Customer].[Address1] as [Customer_Address1],

      [Customer].[Address2] as [Customer_Address2],

      [Customer].[Address3] as [Customer_Address3],

      [Customer].[City] as [Customer_City],

      [Customer].[State] as [Customer_State],

      [Customer].[Zip] as [Customer_Zip],

      [Customer].[Country] as [Customer_Country],

      [ShipTo].[ShipToNum] as [ShipTo_ShipToNum],

      [ShipTo].[Name] as [ShipTo_Name],

      [ShipTo].[Address1] as [ShipTo_Address1],

      [ShipTo].[Address2] as [ShipTo_Address2],

      [ShipTo].[Address3] as [ShipTo_Address3],

      [ShipTo].[City] as [ShipTo_City],

      [ShipTo].[State] as [ShipTo_State],

      [ShipTo].[ZIP] as [ShipTo_ZIP],

      [ShipTo].[Country] as [ShipTo_Country],

      [OrderHed].[PONum] as [OrderHed_PONum],

      [OrderRel].[PartNum] as [OrderRel_PartNum],

      [Part].[PartDescription] as [Part_PartDescription],

      [OrderDtl].[RevisionNum] as [OrderDtl_RevisionNum],

      [Part].[SalesUM] as [Part_SalesUM],

      [Company].[Name] as [Company_Name],

      [Company].[Address1] as [Company_Address1],

      [Company].[Address2] as [Company_Address2],

      [Company].[Address3] as [Company_Address3],

      [Company].[City] as [Company_City],

      [Company].[State] as [Company_State],

      [Company].[Zip] as [Company_Zip],

      [Company].[Country] as [Company_Country]

   

  from Erp.OrderHed as OrderHed

  inner join Erp.OrderDtl as OrderDtl on 

      OrderHed.Company = OrderDtl.Company

  And

      OrderHed.OrderNum = OrderDtl.OrderNum

   

  inner join Erp.OrderRel as OrderRel on 

      OrderDtl.Company = OrderRel.Company

  And

      OrderDtl.OrderNum = OrderRel.OrderNum

  And

      OrderDtl.OrderLine = OrderRel.OrderLine

   

  inner join Erp.Company as Company on 

      OrderHed.Company = Company.Company

   

  inner join Erp.Customer as Customer on 

      OrderHed.Company = Customer.Company

  And

      OrderHed.CustNum = Customer.CustNum

   

  left outer join Erp.ShipTo as ShipTo on 

      OrderHed.Company = ShipTo.Company

  And

      OrderHed.CustNum = ShipTo.CustNum

  And

      OrderHed.ShipToNum = ShipTo.ShipToNum

   

  inner join Erp.Part as Part on 

      OrderRel.Company = Part.Company

  And

      OrderRel.PartNum = Part.PartNum

  0
• 

  Patricia Schwarz

  ★ BarTender Hero ★

  • 25. August 2016 15:00

  Kudos to mentioning a SQL view !!  My life is so much easier now!!!  Works wonderfully - 

  0

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.