将功能应用于视图(Apply a function to a View)

将功能应用于视图(Apply a function to a View)

我在SQL 2012中工作。我曾经在SQL 2008 R2上的单个表视图上使用交叉应用命令。 我正在做一个多表视图，并尝试使用交叉应用，我收到一个错误。 我不确定连接是否导致问题，或者它是否与CROSS APPLY相关。

以下是以前的工作原理：

CREATE VIEW [dbo].[AttributeValues_VW] AS select userProfileID, Name, Item from dbo.AttributeValues cross apply dbo.udf_Split(Value,',') as Item; GO

这是我正在尝试的：

Create View [dbo].[DistrictManager_VW] AS Select Distinct AV.UserProfileID, AV.Name, AV.Value, UP.EmailAddress, MR.RoleID From Mup_UserAttributeValue AV Join Mup_UserProfile UP on AV.UserProfileID=UP.UserProfileID Join Mup_Role MR on MR.UserProfileID=AV.UserProfileID Join Main_Form MF on MF.[Primary Email]=UP.EmailAddress Where RoleID=267 cross apply dbo.udf_Split(AV.Value,',') as Item; Go

I am working in SQL 2012. I used to use a cross apply command on a single table view on SQL 2008 R2. I'm doing a multiple table view and trying to use the cross apply and I am receiving an error. I'm not sure if the join is causing the issue or if it's related to the CROSS APPLY.

Here is what used to work:

CREATE VIEW [dbo].[AttributeValues_VW] AS select userProfileID, Name, Item from dbo.AttributeValues cross apply dbo.udf_Split(Value,',') as Item; GO

Here is what I am trying:

Create View [dbo].[DistrictManager_VW] AS Select Distinct AV.UserProfileID, AV.Name, AV.Value, UP.EmailAddress, MR.RoleID From Mup_UserAttributeValue AV Join Mup_UserProfile UP on AV.UserProfileID=UP.UserProfileID Join Mup_Role MR on MR.UserProfileID=AV.UserProfileID Join Main_Form MF on MF.[Primary Email]=UP.EmailAddress Where RoleID=267 cross apply dbo.udf_Split(AV.Value,',') as Item; Go

最满意答案

cross apply在from子句中：

Create View [dbo].[DistrictManager_VW] AS Select Distinct AV.UserProfileID, AV.Name, AV.Value, UP.EmailAddress, MR.RoleID From Mup_UserAttributeValue AV Join Mup_UserProfile UP on AV.UserProfileID = UP.UserProfileID Join Mup_Role MR on MR.UserProfileID = AV.UserProfileID Join Main_Form MF on MF.[Primary Email] = UP.EmailAddress cross apply dbo.udf_Split(AV.Value, ',') as Item; Where RoleID = 267;

但是，您似乎没有使用Item 。 应该在select子句中。

cross apply goes in the from clause:

Create View [dbo].[DistrictManager_VW] AS Select Distinct AV.UserProfileID, AV.Name, AV.Value, UP.EmailAddress, MR.RoleID From Mup_UserAttributeValue AV Join Mup_UserProfile UP on AV.UserProfileID = UP.UserProfileID Join Mup_Role MR on MR.UserProfileID = AV.UserProfileID Join Main_Form MF on MF.[Primary Email] = UP.EmailAddress cross apply dbo.udf_Split(AV.Value, ',') as Item; Where RoleID = 267;

However, you don't seem to be using Item. Should that be in the select clause.