How to join same table twice using PXSelectJoinOrderBy

Question

I am trying to create view using PXSelectJoinOrderBy for the following SQL statement

SELECT * FROM INVENTORYITEM II
LEFT JOIN INItemXRef X1 ON II.InventoryID = X1.InventoryID AND 
X1.AlternateType = '0VPN' LEFT JOIN INItemXRef X2 ON II.InventoryID = X1.InventoryID AND
X2.AlternateType = '0CPN'

I have used the following statement to join INItemXRef table once and it is working fine.

public PXSelectJoinOrderBy<InventoryItem, LeftJoin<INItemXRef, On<INItemXRef.inventoryID, Equal<InventoryItem.inventoryID>>>, OrderBy<Asc<InventoryItem.inventoryCD>>> FilteredItems;

How to join INItemXRef again with different alias?

score 7 · Accepted Answer · edited May 23 '17 at 12:31

You can obtain the result you want by creating 2 class that inherit the class INItemXRef and using them in the bql query.

public PXSelectJoinOrderBy<InventoryItem,
    LeftJoin<VendorINItemXRef, 
        On<InventoryItem.inventoryID,
            Equal<VendorINItemXRef.inventoryID>,
            And<VendorINItemXRef.alternateType,
                Equal<string_0VPN>>>,
    LeftJoin<CustomerINItemXRef,
        On<InventoryItem.inventoryID,
            Equal<CustomerINItemXRef.inventoryID>,
            And<CustomerINItemXRef.alternateType,
                Equal<string_0CPN>>>>>,
    OrderBy<Asc<InventoryItem.inventoryCD>>> FilteredItems;

public class VendorINItemXRef : INItemXRef
{
    public new class inventoryID : IBqlField{}
    public new class alternateType : IBqlField{}
}

public class CustomerINItemXRef : INItemXRef
{
    public new class inventoryID : IBqlField { }
    public new class alternateType : IBqlField { }
}

You also need to override the parameters that will be used for the comparison so the system can bound them.

For additional information you can also check this question : Acumatica BQL Query with the same table more than once.

+1 for referring to another Acumatica question on StackOverflow! There are other ways to achieve the same result, such as using a PXProjection or a PXDBScalar, but it is by far the most simple way to do it. Thanks Samuel! — Gabriel, Apr 27 '16 at 21:20

How to join same table twice using PXSelectJoinOrderBy

1 Answers1