SELECT dbo.COM_Inventory.FID AS CFID, dbo.COM_Inventory.FType AS CFType,
dbo.COM_Inventory.FBillID AS CFBillID, dbo.COM_Inventory.FEmpID AS CFEmpID,
dbo.COM_Inventory.FDeptID AS CFDeptID,
dbo.COM_Inventory.FOrderID AS CFOrderID,
dbo.COM_Inventory.FCheckerID AS CFCheckerID,
dbo.COM_Inventory.FStockerID AS CFStockerID,
dbo.COM_Inventory.FPreparerID AS CFPreparerID,
dbo.COM_Inventory.FOrgID AS CFOrgID,
dbo.COM_Inventory.FDateBusiness AS CFDateBusiness,
dbo.COM_Inventory.FDate AS CFDate,
dbo.COM_Inventory.FDeliveryAdd AS CFDeliveryAdd,
dbo.COM_Inventory.FDeliveryDate AS CFDeliveryDate,
dbo.COM_Inventory.FCompanyID AS CFCompanyID,
dbo.COM_InventoryEntry.FItemID, CONVERT(decimal(10, 3),
dbo.COM_InventoryEntry.FQty) AS FQty, dbo.COM_InventoryEntry.FDate,
dbo.COM_InventoryEntry.FBatchNO, dbo.COM_InventoryEntry.FQtyDue,
dbo.COM_InventoryEntry.FAmtDue, dbo.COM_InventoryEntry.FOrderBillType,
dbo.COM_InventoryEntry.FOrderBillID, dbo.COM_InventoryEntry.FOrderBillFEntryID,
dbo.COM_InventoryEntry.FOrderBillFID, dbo.COM_InventoryEntry.FSrcBillID,
dbo.COM_InventoryEntry.FSrcBillType, dbo.COM_InventoryEntry.FScrBillFID,
dbo.COM_InventoryEntry.FScrBillFEntryID, Product.FName AS PFName,
Product.FNumber AS PFnumber, dbo.COM_InventoryEntry.FAmtRef,
Employee.FName AS EFName, Department.FName AS DFName,
Stock.FName AS SFNameIN, Stock.FNumber AS SFNumber,
Stock1.FName AS SFNameOut, Stock1.FNumber AS SFNumber1,
Supplier.FName AS SUName, dbo.COM_InventoryEntry.FStockIDIn,
dbo.COM_InventoryEntry.FStockIDOut, dbo.COM_Inventory.FFreeItem1 AS CFItem1,
dbo.COM_Inventory.FFreeItem3 AS CFItem3,
dbo.COM_Inventory.FFreeItem4 AS CFItem4,
dbo.COM_Inventory.FFreeItem7 AS CFItem7,
dbo.COM_Inventory.FFreeItem10 AS CFItem10,
dbo.COM_Inventory.FFreeItem13 AS CFItem13,
dbo.COM_Inventory.FFreeItem8 AS CFItem8, supplier1.FItemID AS SFItemID,
dbo.COM_Inventory.FFreeItem12 AS CFItem12,
dbo.COM_InventoryEntry.FFreeItem1 AS COFItem1,
dbo.COM_InventoryEntry.FFreeItem4 AS COFItem4,
dbo.COM_InventoryEntry.FFreeItem7 AS COFItem7,
dbo.COM_InventoryEntry.FFreeItem10 AS COFItem10,
dbo.COM_InventoryEntry.FFreeItem11 AS COFItem11,
dbo.COM_InventoryEntry.FFreeItem12 AS COFItem12,
dbo.COM_InventoryEntry.FFreeItem2 AS COFItem2,
dbo.COM_InventoryEntry.FFreeItem6 AS COFItem6,
dbo.t_User.FName AS CheckUName, t_User_1.FName AS PreparerUName,
MeasureUnit.FName AS MFName, dbo.COM_InventoryEntry.FMeasureUnitID,
dbo.COM_InventoryEntry.FNote AS COFNote, supplier1.FName AS CFItem11,
supplier3.FName AS CFItem2, SCO.FName AS CFItem14
FROM dbo.COM_Inventory INNER JOIN
dbo.t_User ON dbo.COM_Inventory.FCheckerID = dbo.t_User.FUserID INNER JOIN
dbo.t_User t_User_1 ON
dbo.COM_Inventory.FPreparerID = t_User_1.FUserID LEFT OUTER JOIN
dbo.COM_InventoryEntry ON
dbo.COM_Inventory.FID = dbo.COM_InventoryEntry.FID LEFT OUTER JOIN
(SELECT FItemID, FName
FROM t_Item
WHERE FNumber LIKE '003%' AND Flevel > 3) Employee ON
Employee.FItemID = dbo.COM_Inventory.FEmpID LEFT OUTER JOIN
(SELECT FItemID, FName
FROM t_Item
WHERE (FNumber LIKE '002' + '%') AND (FLevel > 2)) Department ON
Department.FItemID = dbo.COM_Inventory.FDeptID LEFT OUTER JOIN
(SELECT *
FROM t_Item
WHERE FNumber LIKE '005%' AND FLevel >= 3) Stock ON
Stock.FItemID = dbo.COM_InventoryEntry.FStockIDIn LEFT OUTER JOIN
(SELECT *
FROM t_Item
WHERE FNumber LIKE '005%' AND FLevel >= 3) Stock1 ON
Stock1.FItemID = dbo.COM_InventoryEntry.FStockIDOut LEFT OUTER JOIN
(SELECT *
FROM t_Item
WHERE (FNumber LIKE '11%' OR
Fnumber LIKE '17%' OR
FNumber LIKE '16.%') AND FLevel >= 2) Product ON
Product.FItemID = dbo.COM_InventoryEntry.FItemID LEFT OUTER JOIN
(SELECT FItemID, FName
FROM t_Item
WHERE (FNumber LIKE '008%') AND (FLevel >= 3)) Supplier ON
Supplier.FItemID = dbo.COM_Inventory.FCompanyID LEFT OUTER JOIN
(SELECT FItemID, FNumber, FName
FROM t_Item
WHERE (FParentID = '619')) MeasureUnit ON
dbo.COM_InventoryEntry.FMeasureUnitID = MeasureUnit.FItemID LEFT OUTER JOIN
(SELECT FItemID, FNumber, FName
FROM t_Item
WHERE FNumber LIKE '008%') supplier1 ON
supplier1.FItemID = dbo.COM_Inventory.FFreeItem11 LEFT OUTER JOIN
(SELECT *
FROM t_Item
WHERE Fnumber LIKE '013%') supplier3 ON CONVERT(nvarchar(100),
supplier3.FItemID) = dbo.COM_Inventory.FFreeItem2 LEFT OUTER JOIN
(SELECT *
FROM t_Item
WHERE Fnumber LIKE '016.%') SCO ON CONVERT(nvarchar(100), SCO.FItemID)
= dbo.COM_Inventory.FFreeItem14
以上小弟建立的单据视图,再对这个进行数据查询和汇总时很慢需要10分钟
小弟对这个视图建立索引,但是显示如下信息:无法在视图 'ZM_View_ComInventory' 上创建 索引,因为该视图未绑定到架构。请各位大哥帮忙!!
dbo.COM_Inventory.FBillID AS CFBillID, dbo.COM_Inventory.FEmpID AS CFEmpID,
dbo.COM_Inventory.FDeptID AS CFDeptID,
dbo.COM_Inventory.FOrderID AS CFOrderID,
dbo.COM_Inventory.FCheckerID AS CFCheckerID,
dbo.COM_Inventory.FStockerID AS CFStockerID,
dbo.COM_Inventory.FPreparerID AS CFPreparerID,
dbo.COM_Inventory.FOrgID AS CFOrgID,
dbo.COM_Inventory.FDateBusiness AS CFDateBusiness,
dbo.COM_Inventory.FDate AS CFDate,
dbo.COM_Inventory.FDeliveryAdd AS CFDeliveryAdd,
dbo.COM_Inventory.FDeliveryDate AS CFDeliveryDate,
dbo.COM_Inventory.FCompanyID AS CFCompanyID,
dbo.COM_InventoryEntry.FItemID, CONVERT(decimal(10, 3),
dbo.COM_InventoryEntry.FQty) AS FQty, dbo.COM_InventoryEntry.FDate,
dbo.COM_InventoryEntry.FBatchNO, dbo.COM_InventoryEntry.FQtyDue,
dbo.COM_InventoryEntry.FAmtDue, dbo.COM_InventoryEntry.FOrderBillType,
dbo.COM_InventoryEntry.FOrderBillID, dbo.COM_InventoryEntry.FOrderBillFEntryID,
dbo.COM_InventoryEntry.FOrderBillFID, dbo.COM_InventoryEntry.FSrcBillID,
dbo.COM_InventoryEntry.FSrcBillType, dbo.COM_InventoryEntry.FScrBillFID,
dbo.COM_InventoryEntry.FScrBillFEntryID, Product.FName AS PFName,
Product.FNumber AS PFnumber, dbo.COM_InventoryEntry.FAmtRef,
Employee.FName AS EFName, Department.FName AS DFName,
Stock.FName AS SFNameIN, Stock.FNumber AS SFNumber,
Stock1.FName AS SFNameOut, Stock1.FNumber AS SFNumber1,
Supplier.FName AS SUName, dbo.COM_InventoryEntry.FStockIDIn,
dbo.COM_InventoryEntry.FStockIDOut, dbo.COM_Inventory.FFreeItem1 AS CFItem1,
dbo.COM_Inventory.FFreeItem3 AS CFItem3,
dbo.COM_Inventory.FFreeItem4 AS CFItem4,
dbo.COM_Inventory.FFreeItem7 AS CFItem7,
dbo.COM_Inventory.FFreeItem10 AS CFItem10,
dbo.COM_Inventory.FFreeItem13 AS CFItem13,
dbo.COM_Inventory.FFreeItem8 AS CFItem8, supplier1.FItemID AS SFItemID,
dbo.COM_Inventory.FFreeItem12 AS CFItem12,
dbo.COM_InventoryEntry.FFreeItem1 AS COFItem1,
dbo.COM_InventoryEntry.FFreeItem4 AS COFItem4,
dbo.COM_InventoryEntry.FFreeItem7 AS COFItem7,
dbo.COM_InventoryEntry.FFreeItem10 AS COFItem10,
dbo.COM_InventoryEntry.FFreeItem11 AS COFItem11,
dbo.COM_InventoryEntry.FFreeItem12 AS COFItem12,
dbo.COM_InventoryEntry.FFreeItem2 AS COFItem2,
dbo.COM_InventoryEntry.FFreeItem6 AS COFItem6,
dbo.t_User.FName AS CheckUName, t_User_1.FName AS PreparerUName,
MeasureUnit.FName AS MFName, dbo.COM_InventoryEntry.FMeasureUnitID,
dbo.COM_InventoryEntry.FNote AS COFNote, supplier1.FName AS CFItem11,
supplier3.FName AS CFItem2, SCO.FName AS CFItem14
FROM dbo.COM_Inventory INNER JOIN
dbo.t_User ON dbo.COM_Inventory.FCheckerID = dbo.t_User.FUserID INNER JOIN
dbo.t_User t_User_1 ON
dbo.COM_Inventory.FPreparerID = t_User_1.FUserID LEFT OUTER JOIN
dbo.COM_InventoryEntry ON
dbo.COM_Inventory.FID = dbo.COM_InventoryEntry.FID LEFT OUTER JOIN
(SELECT FItemID, FName
FROM t_Item
WHERE FNumber LIKE '003%' AND Flevel > 3) Employee ON
Employee.FItemID = dbo.COM_Inventory.FEmpID LEFT OUTER JOIN
(SELECT FItemID, FName
FROM t_Item
WHERE (FNumber LIKE '002' + '%') AND (FLevel > 2)) Department ON
Department.FItemID = dbo.COM_Inventory.FDeptID LEFT OUTER JOIN
(SELECT *
FROM t_Item
WHERE FNumber LIKE '005%' AND FLevel >= 3) Stock ON
Stock.FItemID = dbo.COM_InventoryEntry.FStockIDIn LEFT OUTER JOIN
(SELECT *
FROM t_Item
WHERE FNumber LIKE '005%' AND FLevel >= 3) Stock1 ON
Stock1.FItemID = dbo.COM_InventoryEntry.FStockIDOut LEFT OUTER JOIN
(SELECT *
FROM t_Item
WHERE (FNumber LIKE '11%' OR
Fnumber LIKE '17%' OR
FNumber LIKE '16.%') AND FLevel >= 2) Product ON
Product.FItemID = dbo.COM_InventoryEntry.FItemID LEFT OUTER JOIN
(SELECT FItemID, FName
FROM t_Item
WHERE (FNumber LIKE '008%') AND (FLevel >= 3)) Supplier ON
Supplier.FItemID = dbo.COM_Inventory.FCompanyID LEFT OUTER JOIN
(SELECT FItemID, FNumber, FName
FROM t_Item
WHERE (FParentID = '619')) MeasureUnit ON
dbo.COM_InventoryEntry.FMeasureUnitID = MeasureUnit.FItemID LEFT OUTER JOIN
(SELECT FItemID, FNumber, FName
FROM t_Item
WHERE FNumber LIKE '008%') supplier1 ON
supplier1.FItemID = dbo.COM_Inventory.FFreeItem11 LEFT OUTER JOIN
(SELECT *
FROM t_Item
WHERE Fnumber LIKE '013%') supplier3 ON CONVERT(nvarchar(100),
supplier3.FItemID) = dbo.COM_Inventory.FFreeItem2 LEFT OUTER JOIN
(SELECT *
FROM t_Item
WHERE Fnumber LIKE '016.%') SCO ON CONVERT(nvarchar(100), SCO.FItemID)
= dbo.COM_Inventory.FFreeItem14
以上小弟建立的单据视图,再对这个进行数据查询和汇总时很慢需要10分钟
小弟对这个视图建立索引,但是显示如下信息:无法在视图 'ZM_View_ComInventory' 上创建 索引,因为该视图未绑定到架构。请各位大哥帮忙!!
try
该错误消息本身并未指出该错误产生的原因或建议你如何避免该错误。要解决该问题,你应该为该函数增加SCHEMABINDING选项。eg:ALTER FUNCTION dbo.fn_add1(@i AS INT) RETURNS INT WITH SCHEMABINDINGASBEGIN RETURN @i + 1;ENDGO
create view testView
with schemabinding
as
select *
from test