Sql command - Multi part identifier coud nt be bound

Question

This is my code , where i cannot bound DTCategory
select dc.Name as Category
,dc.Id as CategoryId,vv.OpeningStock as OpeningStock
,vv.StockEntry as StockEntry
,vv.SalesInvoice as Sales
,vv.PurchaseInvoice as Purchase
,vv.SalesReturn as SalesReturn
,vv.PurchaseReturn as PurchaseReturn
,vv.StockBalance as StockBalance

                                                from DTCategory dc  
                                                left join(  
                                                select ca.Id as CategoryId,   
                                                sum(case when bt.Name='Opening Stock' then (bd.Quantity * (case when mp.PrimaryMeasuringConversion > 0 then mp.PrimaryMeasuringConversion else 1 end)) else 0 end)   
                                                + SUM(case when bt.Name <> 'Opening Stock' and b.BillDate < '2020-12-31 09:00:00 PM' then (bd.Quantity* bt.StockEffect * (case when mp.PrimaryMeasuringConversion > 0 then mp.PrimaryMeasuringConversion else 1 end)) else 0 end)  
                                                as OpeningStock,  
                                                sum(case when bt.Name='Stock Entry' and ( b.BillDate >= '2020-12-31 09:00:00 PM' and b.BillDate <= '2022-06-20 08:59:59 PM') then (bd.Quantity * (case when mp.PrimaryMeasuringConversion > 0 then mp.PrimaryMeasuringConversion else 1 end)) else 0 end) as StockEntry,  
                                                sum(case when bt.Name='Sales Invoice' and ( b.BillDate >= '2020-12-31 09:00:00 PM' and b.BillDate <= '2022-06-20 08:59:59 PM') then (bd.Quantity * (case when mp.PrimaryMeasuringConversion > 0 then mp.PrimaryMeasuringConversion else 1 end)) else 0 end) as SalesInvoice,  
                                                sum(case when bt.Name='Purchase Invoice' and ( b.BillDate >= '2020-12-31 09:00:00 PM' and b.BillDate <= '2022-06-20 08:59:59 PM') then (bd.Quantity * (case when mp.PrimaryMeasuringConversion > 0 then mp.PrimaryMeasuringConversion else 1 end)) else 0 end) as PurchaseInvoice,  
                                                sum(case when bt.Name='Sales Return' and ( b.BillDate >= '2020-12-31 09:00:00 PM' and b.BillDate <= '2022-06-20 08:59:59 PM') then  (bd.Quantity * (case when mp.PrimaryMeasuringConversion > 0 then mp.PrimaryMeasuringConversion else 1 end)) else 0 end) as SalesReturn,  
                                                sum(case when bt.Name='Purchase Return' and ( b.BillDate >= '2020-12-31 09:00:00 PM' and b.BillDate <= '2022-06-20 08:59:59 PM') then (bd.Quantity * (case when mp.PrimaryMeasuringConversion > 0 then mp.PrimaryMeasuringConversion else 1 end)) else 0 end) as PurchaseReturn,  
                                                sum(case when (bt.Name='Opening Stock' or b.BillDate <= '2022-06-20 08:59:59 PM') then bd.Quantity * bt.StockEffect * (case when mp.PrimaryMeasuringConversion > 0 then mp.PrimaryMeasuringConversion else 1 end)else 0 end) as StockBalance  
                                                  
                                                  from BillDetails bd   
                                                      inner join Bill b on b.Id=bd.BillId  
                                                      inner join DTBillType bt on bt.Id=b.BillTypeId  
                                                      inner join DTProduct p on p.Id=bd.ProductId  
                                                      inner join DTCategory ca on ca.Id=p.ProductCategoryId  
                                                      left join MeasuringUnitPrice mp on mp.ProductId=bd.ProductId and mp.MeasuringUnitId=bd.MeasuringUnitId  
                                                      where p.Deleted=0 and p.IsService=0 and  bd.Deleted=0  and b.Deleted=0 and b.FinancialYearId='56'   
                                                      and (0 = 0 or b.WareHouseId='0')    
                                                      and p.StoreId='66'  and bt.StoreId='66' and bd.StoreId='66' and b.StoreId='66' and ca.StoreId='66' and mp.StoreId='66'  
                                                      group by ca.Id) vv on vv.CategoryId=dc.Id  
                                                       where dc.Deleted=0 and dc.StoreId='66'  
                                                      and (0 = 0 or dc.Id='0')  
                                                      order by dc.Name  

Please suggest me , thank you in advance

Answer 1

As Tom says, we would need to see the full error message, including line number.

But the error message usually means that you have a column prefix you have not defined. For instance:

   SELECT x.name FROM sys.objects  

`results in

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "x.name" could not be bound.

Answer 2

Hi,@rashmitha r

I agree with both experts that you should tell us which column is showing the error . In addition, I don't understand your logic here , where 0=0 what you want to do here, I found that you use a lot of where statements . for example, the p table and the bd table have been linked through inner join , so you don't need to write storeid='66' twice , maybe you should modify the link condition like this:

from BillDetails bd  
                                inner join Bill b on b.Id=bd.BillId  
                                inner join DTBillType bt on bt.Id=b.BillTypeId  
                                inner join DTProduct p on p.Id=bd.ProductId and p.deleted=bd.deleted  

Bert Zhou

Answer 3

Am getting this error, The multi-part identifier "dc.StoreId" could not be bound.