Acumatica BQL Query with the same table more than

问题:

I am trying to build a BQL query using the same table more than once. In my example below I am trying to use POVendorInventory as it relates to the default for the item and for each item warehouse (reason for the table listed twice in the query).

How do I do this in an Acumatica BQL Query. My current query is:

foreach (PXResult<INItemSite, InventoryItem, POVendorInventory, POVendorInventory> result in PXSelectJoin<INItemSite,
        InnerJoin<InventoryItem, On<INItemSite.inventoryID, Equal<InventoryItem.inventoryID>>,
        LeftJoin<POVendorInventory, On<InventoryItem.inventoryID, Equal<POVendorInventory.inventoryID>
            , And<InventoryItem.preferredVendorID, Equal<POVendorInventory.vendorID>
            , And<InventoryItem.preferredVendorLocationID, Equal<POVendorInventory.vendorLocationID>>>>,
        LeftJoin<POVendorInventory, On<INItemSite.preferredVendorID, Equal<POVendorInventory.vendorID>,
                And<INItemSite.inventoryID, Equal<POVendorInventory.inventoryID>>>>>>,
            Where<InventoryItem.stkItem, Equal<boolTrue>>,
            OrderBy<Asc<INItemSite.inventoryID, Asc<INItemSite.siteID>>>>.Select(graph))
        {
            var poVendorInventoryDefault = (POVendorInventory)result[2];
            var poVendorInventorySite = (POVendorInventory)result[3];
        }

The above results in the following error when the query is executed: The correlation name 'POVendorInventory' is specified multiple times in a FROM clause.

The similar query in MS SQL is:

SELECT  s.InventoryID ,
    i.InventoryCD ,
    s.SiteID ,
    n.SiteCD ,
    s.PreferredVendorID ,
    s.PreferredVendorOverride ,
    pd.VendorID ,
    pd.AddLeadTimeDays ,
    ps.VendorID ,
    ps.AddLeadTimeDays
    FROM    dbo.INItemSite s
    INNER JOIN dbo.InventoryItem i ON s.CompanyID = i.CompanyID
                                      AND s.InventoryID = i.InventoryID
    INNER JOIN dbo.INSite n ON s.CompanyID = n.CompanyID
                               AND s.SiteID = n.SiteID
    LEFT JOIN dbo.POVendorInventory pd ON i.CompanyID = pd.CompanyID
                                          AND i.InventoryID = pd.InventoryID
                                          AND i.PreferredVendorID = pd.VendorID
                                          AND i.PreferredVendorLocationID = pd.VendorLocationID
    LEFT JOIN dbo.POVendorInventory ps ON s.CompanyID = ps.CompanyID
                                          AND s.InventoryID = ps.InventoryID
                                          AND s.PreferredVendorID = ps.VendorID
    WHERE   s.CompanyID = 2
    AND i.StkItem = 1
    ORDER BY s.InventoryID, s.SiteID

The following does not work either.

[Serializable]
public class POVendorInventoryTwo : POVendorInventory{}

PXResult<INItemSite, InventoryItem, POVendorInventoryTwo, POVendorInventory> result in PXSelectJoin<INItemSite,
        InnerJoin<InventoryItem, On<INItemSite.inventoryID, Equal<InventoryItem.inventoryID>>,
        LeftJoin<POVendorInventoryTwo, On<InventoryItem.inventoryID, Equal<POVendorInventoryTwo.inventoryID>
            , And<InventoryItem.preferredVendorID, Equal<POVendorInventoryTwo.vendorID>
            , And<InventoryItem.preferredVendorLocationID, Equal<POVendorInventoryTwo.vendorLocationID>>>>,
        LeftJoin<POVendorInventory, On<INItemSite.preferredVendorID, Equal<POVendorInventory.vendorID>,
                And<INItemSite.inventoryID, Equal<POVendorInventory.inventoryID>>>>>>,
            Where<InventoryItem.stkItem, Equal<boolTrue>>,
            OrderBy<Asc<INItemSite.inventoryID, Asc<INItemSite.siteID>>>>.Select(graph)

But as the answer mentions you need to use POVendorInventoryTwo for the second usage like so:

PXResult<INItemSite, InventoryItem, POVendorInventory, POVendorInventoryTwo> result in PXSelectJoin<INItemSite,
        InnerJoin<InventoryItem, On<INItemSite.inventoryID, Equal<InventoryItem.inventoryID>>,
        LeftJoin<POVendorInventory, On<InventoryItem.inventoryID, Equal<POVendorInventory.inventoryID>
            , And<InventoryItem.preferredVendorID, Equal<POVendorInventory.vendorID>
            , And<InventoryItem.preferredVendorLocationID, Equal<POVendorInventory.vendorLocationID>>>>,
        LeftJoin<POVendorInventoryTwo, On<INItemSite.preferredVendorID, Equal<POVendorInventoryTwo.vendorID>,
                And<INItemSite.inventoryID, Equal<POVendorInventoryTwo.inventoryID>>>>>>,
            Where<InventoryItem.stkItem, Equal<boolTrue>>,
            OrderBy<Asc<INItemSite.inventoryID, Asc<INItemSite.siteID>>>>.Select(graph)

回答1:

What I've done in the past is subclass the DAC and query off that.

Something like this:

public class POVendorInventoryTwo : POVendorInventory
{}

Then in your BQL, use this subclassed version for the second value.