sql-server - SQL Server 2005 - 无法绑定(bind)多部分标识符

Question

这段代码:

USE [db]
GO
/****** Object:  UserDefinedFunction [dbo].[GetFieldPickerReports]    Script Date: 01/11/2013 19:12:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  FUNCTION [dbo].[GetFieldPickerReports] (@CustomerName  varchar(100))
RETURNS TABLE
AS 
  RETURN SELECT [fpr].[ID], [fpr].[Name], [fpr].[Global], [fpr].[FPRCategoryID]
  FROM FieldPickerReport fpr, dbo.GetProductKeyIdByCustomer(@CustomerName) pk 
  LEFT JOIN [FPRCategory] fprcat on ([fpr].[FPRCategoryID]=[fprcat].[ID])
  WHERE [Global]=1 OR ProductKeyID=pk.id

产生错误:

The multi-part identifier "fpr.FPRCategoryID" could not be bound.

此查询在没有 Left Join 的情况下有效(我目前需要添加)或删除 dbo.GetProductKeyIdByCustomer()和 Where条款。

这是 dbo.GetProductKeyIdByCustomer() 的代码

USE [db]
GO
/****** Object:  UserDefinedFunction [dbo].[GetProductKeyIdByCustomer]    Script Date: 01/11/2013 19:58:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  FUNCTION [dbo].[GetProductKeyIdByCustomer] (@CustomerName  varchar(100))
RETURNS TABLE
AS
   RETURN SELECT id
   FROM ProductKey
   WHERE [CustomerName]=@CustomerName AND Enabled=1

我的查询有什么问题？

Answer 1

首先，我认为这些 UDF 会更好地实现为 View 。

其次，您正在混合隐式和显式连接。使用所有显式连接应该可以解决您的问题:

SELECT 
    [fpr].[ID], 
    [fpr].[Name], 
    [fpr].[Global], 
    [fpr].[FPRCategoryID]
FROM 
    FieldPickerReport fpr
    JOIN dbo.GetProductKeyIdByCustomer(@CustomerName) pk 
        ON [Global] = 1 OR ProductKeyID = pk.id
    LEFT JOIN FPRCategory fprcat 
        ON fpr.FPRCategoryID = fprcat.ID