救命啊！无法绑定由多个部分组成的标识符

[commemoration]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetRecordList] 
	@CurrentWarehouse INT,
	@Code NVARCHAR(50),
	@LocationID NVARCHAR(50),
	@Begin DATETIME,
	@End DATETIME,
	@Page INT,
	@PageSize INT,
	@RowCount INT OUTPUT
AS		
	DECLARE @iTemp INT
	IF @CurrentWarehouse = 0
		SET @CurrentWarehouse = '%%'
	ELSE
		SET @CurrentWarehouse=LTRIM(@CurrentWarehouse)
	IF @Code = ''
		SET @Code = '%%'
	SET @RowCount = 0

	SELECT @RowCount = COUNT(1) FROM (
		SELECT L.Amount,L.warehouseID,L.LocationID,Locations.LastUpdate,P.* FROM Products P INNER JOIN LocationStorage L ON P.ID=L.ProductID 
		JOIN dbo.Locations ON Locations.ID=L.LocationID ) AS Checkp WHERE Code LIKE '%'+@Code+'%' AND warehouseID LIKE @CurrentWarehouse
		 AND LocationID LIKE '%'+@LocationID+'%' AND Locations.LastUpdate BETWEEN @Begin AND @End 
	
	IF(@RowCount = 0)
		RETURN 0
	SET @iTemp = (@Page - 1) * @PageSize ;
	WITH tmp_table
	AS
	(
		SELECT ROW_NUMBER() OVER(ORDER BY Code) AS row_id,* FROM (SELECT L.Amount,L.warehouseID,L.LocationID,Locations.LastUpdate,P.* FROM Products P INNER JOIN LocationStorage L ON P.ID=L.ProductID 
		JOIN dbo.Locations ON Locations.ID=L.LocationID) AS Checkp
	)
	SELECT TOP (@PageSize) * FROM tmp_table WHERE  row_id>@iTemp AND Code like '%'+@Code+'%' AND warehouseID LIKE @CurrentWarehouse AND LocationID LIKE  '%'+@LocationID+'%' AND Locations.LastUpdate BETWEEN @Begin AND @End ORDER BY Code ASC

消息 4104，级别 16，状态 1，过程 GetRecordList，第 25 行
无法绑定由多个部分组成的标识符 "Locations.LastUpdate"。
消息 4104，级别 16，状态 1，过程 GetRecordList，第 25 行
无法绑定由多个部分组成的标识符 "Locations.LastUpdate"。
消息 4104，级别 16，状态 1，过程 GetRecordList，第 33 行
无法绑定由多个部分组成的标识符 "Locations.LastUpdate"。
消息 4104，级别 16，状态 1，过程 GetRecordList，第 33 行
无法绑定由多个部分组成的标识符 "Locations.LastUpdate"。

[快溜]

AND LocationID LIKE '%'+@LocationID+'%' AND LastUpdate --这个LastUpdate删掉
BETWEEN @Begin AND @End

[快溜]

--错了，是Locations
AND LocationID LIKE '%'+@LocationID+'%' AND Locations.LastUpdate --这个Locations删掉
BETWEEN @Begin AND @End

[-晴天]

        SELECT L.Amount,L.warehouseID,L.LocationID,Locations.LastUpdate,P.* FROM Products P INNER JOIN LocationStorage L ON P.ID=L.ProductID 
        JOIN dbo.Locations ON Locations.ID=L.LocationID ) AS Checkp WHERE Code LIKE '%'+@Code+'%' AND warehouseID LIKE @CurrentWarehouse
         AND LocationID LIKE '%'+@LocationID+'%' AND Locations.LastUpdate BETWEEN @Begin AND @End  ---这儿的LocationID要指明表名

[commemoration]

谢谢！解决了！不过我很想知道这是为什么。

[快溜]

[Quote=引用 4 楼 commemoration 的回复:]
谢谢！解决了！不过我很想知道这是为什么。
[/Quote]内表可以访问外表，内表却不能被外面的对象访问

[AcHerat]

SELECT @RowCount = COUNT(1) FROM (
SELECT L.Amount,L.warehouseID,L.LocationID,Locations.LastUpdate,P.* FROM Products P INNER JOIN LocationStorage L ON P.ID=L.ProductID
JOIN dbo.Locations ON Locations.ID=L.LocationID ) AS Checkp WHERE Code LIKE '%'+@Code+'%' AND warehouseID LIKE @CurrentWarehouse
AND LocationID LIKE '%'+@LocationID+'%' AND Locations.LastUpdate BETWEEN @Begin AND @End


因为你红色部分有起别名，可以换成Checkp.LastUpdate试试。

[commemoration]

谢谢了！！