Double Sql Join Problem Seguir

0
Avatar
Legacy Poster

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.

6 comentarios

0
Avatar
Legacy Poster
Acciones de comentarios Permalink

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
Avatar
Legacy Poster
Acciones de comentarios Permalink

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
Avatar
Domingo Rodriguez
Moderador
Acciones de comentarios Permalink

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
Avatar
Legacy Poster
Acciones de comentarios Permalink


"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
Avatar
Legacy Poster
Acciones de comentarios Permalink

"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
Avatar
Patricia Schwarz
Acciones de comentarios Permalink

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

Iniciar sesión para dejar un comentario.