SQL - 无法绑定多部分标识符

I have an SQL query that I am trying to edit. It returns the error :

我有一个我想编辑的SQL查询。它返回错误:

"The multi-part identifier "i.LastPurPrc" could not be bound." when I try to add a column 'Amount1'. (Error on 2nd line of query code)

“无法绑定多部分标识符”i.LastPurPrc“。当我尝试添加列'Amount1'时。 (第二行查询代码出错)

The query:

Select a.Itemcode, max(a.Dscription) as ItemName,
sum(a.OpeningBalance) as OpeningBalance, sum(a.OpeningBalance) * i.LastPurPrc AS 'Amount1', sum(a.INq) as 'IN', sum(a.OUT) as OUT,
((sum(a.OpeningBalance) + sum(a.INq)) - sum(a.OUT)) as Closing,
(Select i.InvntryUom from OITM i 
Where i.ItemCode = a.Itemcode) as UOM
from 
(Select N1.Warehouse, N1.Itemcode, N1.Dscription, (sum(N1.inqty)-sum(n1.outqty))
as OpeningBalance, 0 as INq, 0 as OUT 
from dbo.OINM N1
Where N1.DocDate < '04-01-2015' and N1.Warehouse = 'WNR02' 
Group By N1.Warehouse,N1.ItemCode,
N1.Dscription 
Union All 
Select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance,
sum(N1.inqty), 0 as OUT 
from dbo.OINM N1 
Where N1.DocDate >= '04-01-2015' and N1.DocDate <= '04-30-2015'
and N1.Inqty > 0 and N1.Warehouse = 'WNR02' 
Group By N1.Warehouse, N1.ItemCode, N1.Dscription
Union All 
Select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance, 0 , sum(N1.outqty) as OUT
From dbo.OINM N1 
Where N1.DocDate >= '04-01-2015' and N1.DocDate <= '04-30-2015' and N1.OutQty > 0
and N1.Warehouse = 'WNR02' 
Group By N1.Warehouse,N1.ItemCode,N1.Dscription) a, dbo.OITM I1
where a.ItemCode = I1.ItemCode
Group By a.Itemcode Having sum(a.OpeningBalance) + sum(a.INq) + sum(a.OUT) > 0 Order By a.Itemcode

How do I solve this?

我该如何解决这个问题?

1 个解决方案

#1

Your i table is in a subselect, you cannot reference that table in the outer field list.

您的i表位于子选择中,您无法在外部字段列表中引用该表。

You would need to join the table instead of adding it as a subselect to be able to reference it.

您需要加入表而不是将其添加为子选择以便能够引用它。

Since you are grouping by itemid you should probably also sum it.

由于您按itemid进行分组,因此您应该对其进行求和。

Since we have no demo data I'm not sure how many IUOM records there are for each itemid, but if that's a 1:1 this should work. If not you'll have to join to an aliassed query instead of the table itself.

由于我们没有演示数据,我不确定每个itemid有多少IUOM记录,但如果这是1:1,这应该有效。如果不是,您将不得不加入到别名查询而不是表本身。

SELECT a.Itemcode
    ,max(a.Dscription) AS ItemName
    ,sum(a.OpeningBalance) AS OpeningBalance
    ,sum(a.OpeningBalance) * sum(i.LastPurPrc) AS 'Amount1'
    ,sum(a.INq) AS 'IN'
    ,sum(a.OUTPUT) AS OUTPUT
    ,((sum(a.OpeningBalance) + sum(a.INq)) - sum(a.OUTPUT)) AS Closing

FROM (
    SELECT N1.Warehouse
        ,N1.Itemcode
        ,N1.Dscription
        ,(sum(N1.inqty) - sum(n1.outqty)) AS OpeningBalance
        ,0 AS INq
        ,0 AS OUTPUT
    FROM dbo.OINM N1
    WHERE N1.DocDate < '04-01-2015'
        AND N1.Warehouse = 'WNR02'
    GROUP BY N1.Warehouse
        ,N1.ItemCode
        ,N1.Dscription

    UNION ALL

    SELECT N1.Warehouse
        ,N1.Itemcode
        ,N1.Dscription
        ,0 AS OpeningBalance
        ,sum(N1.inqty)
        ,0 AS OUTPUT
    FROM dbo.OINM N1
    WHERE N1.DocDate >= '04-01-2015'
        AND N1.DocDate <= '04-30-2015'
        AND N1.Inqty > 0
        AND N1.Warehouse = 'WNR02'
    GROUP BY N1.Warehouse
        ,N1.ItemCode
        ,N1.Dscription

    UNION ALL

    SELECT N1.Warehouse
        ,N1.Itemcode
        ,N1.Dscription
        ,0 AS OpeningBalance
        ,0
        ,sum(N1.outqty) AS OUTPUT
    FROM dbo.OINM N1
    WHERE N1.DocDate >= '04-01-2015'
        AND N1.DocDate <= '04-30-2015'
        AND N1.OutQty > 0
        AND N1.Warehouse = 'WNR02'
    GROUP BY N1.Warehouse
        ,N1.ItemCode
        ,N1.Dscription
    ) a

JOIN OITM i 
ON i.itemcode = a.itemcode
JOIN dbo.OITM I1
ON  a.ItemCode =I1.ItemCode

GROUP BY a.Itemcode
HAVING sum(a.OpeningBalance) + sum(a.INq) + sum(a.OUTPUT) > 0
ORDER BY a.Itemcode

You should also try formatting your queries and indenting them better. It would help you spot the logic better. If it's inherited code just pull it through any online sql formatter, I use poorsql

您还应该尝试格式化查询并更好地缩进查询。它可以帮助您更好地发现逻辑。如果它是继承的代码,只需通过任何在线sql格式化程序,我使用poorsql

Also you should no longer use old-style joins

此外,您不应再使用旧式连接