SQL - The multi-part identifier could not be bound

Question

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

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

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?

Answer 1

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

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.

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.

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

Also you should no longer use old-style joins