同一张表的Acumatica BQL查询不止一次 [英] Acumatica BQL Query with the same table more than once

问题描述

我试图多次使用同一张表构建BQL查询。在下面的示例中，我尝试使用POVendorInventory，因为它与物料和每个物料仓库的默认值相关（查询中两次列出的表的原因）。

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).

如何在Acumatica BQL查询中执行此操作。我当前的查询是：

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];
        }

执行查询时，以上结果导致以下错误：
在FROM子句中多次指定了相关名称'POVendorInventory'。

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

MS SQL中类似的查询是：

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

以下方法也不起作用。

[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)

答案中提到您需要对第二种用法使用POVendorInventoryTwo，例如：

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)

推荐答案

我过去所做的是将DAC子类化并对其进行查询。

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

类似这样的事情：

public class POVendorInventoryTwo : POVendorInventory
{}

然后在您的BQL中，将此子类版本用作第二个值。

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

这篇关于同一张表的Acumatica BQL查询不止一次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！