无法找到任何列“dbo”或者用户定义的函数或聚合“dbo.fnRegistrationNoExp”，或者名称不明确。 [英] Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnRegistrationNoExp", or the name is ambiguous.

问题描述

在这里，我生成WardNo + 6位=注册号...我怎样才能测试功能.... Bold Capital中的错误请.....给出解决方案......

我在我的一个SQL Server 2008 r2查询中遇到了令人沮丧的错误。它解析得很好，但在我尝试执行时崩溃了。我得到的错误如下：



消息8120，等级16，状态1，行1

列''dbo.FamilyHead。 WardID''在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。



fnFormatSerialNoPostfix ： - 它的功能genrate 6 digite no it'是... ok

Here, I am generate WardNo+6 digit = Registration No...How can i test the funtion ....error in Bold Capital words please .....give solution...
I''m getting a frustrating error in one of my SQL Server 2008 r2 queries. It parses fine, but crashes when I try to execute. The error I get is the following :

Msg 8120, Level 16, State 1, Line 1
Column ''dbo.FamilyHead.WardID'' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

fnFormatSerialNoPostfix:- it''s function genrate 6 digite no it''s...ok

SET @RegistrationNo=(SELECT CONVERT(Varchar(15),CONVERT(varchar,@WardID) + 'W-') +         CONVERT(varchar(6),dbo.fnFormatSerialNoPostfix(CONVERT(int,SUBSTRING(MAX(RegistrationNo),10,6)+1))) FROM dbo.ParivarCertificate INNER JOIN dbo.FamilyMember ON dbo.ParivarCertificate.FamilyMemberID = dbo.FamilyMember.FamilyMemberID  INNER JOIN dbo.FamilyHead ON dbo.FamilyMember.FamilyID = dbo.FamilyHead.FamilyID WHERE dbo.FamilyHead.WardID = @WardID )

如何修复它。 ...所有代码如下...

How to fix it.... All Code bellow...

ALTER FUNCTION [dbo].[fnRegitrationNoExp] (@WardID int)

RETURNS Varchar(12)
AS
BEGIN
    -- Declare the return variable here
        DECLARE @RegistrationNo Varchar(15)
        if Exists(SELECT RegistrationNo FROM dbo.ParivarCertificate
                  INNER JOIN dbo.FamilyMember ON dbo.ParivarCertificate.FamilyMemberID = dbo.FamilyMember.FamilyMemberID
                  INNER JOIN dbo.FamilyHead ON dbo.FamilyMember.FamilyID = dbo.FamilyHead.FamilyID
                  WHERE dbo.FamilyHead.WardID =@WardID)
                  --WHERE FH.WardID=@Ward)

            Begin

                SET @RegistrationNo=(SELECT CONVERT(Varchar(15),CONVERT(varchar,@WardID) + 'W-') +
                CONVERT(varchar(6),dbo.fnFormatSerialNoPostfix(CONVERT(int,SUBSTRING(MAX(RegistrationNo),10,6)+1)))
                FROM dbo.ParivarCertificate
                  INNER JOIN dbo.FamilyMember ON dbo.ParivarCertificate.FamilyMemberID = dbo.FamilyMember.FamilyMemberID
                  INNER JOIN dbo.FamilyHead ON dbo.FamilyMember.FamilyID = dbo.FamilyHead.FamilyID
                  WHERE dbo.FamilyHead.WardID = @WardID )

            End
        Else
        Begin

                SET @RegistrationNo=(SELECT CONVERT(varchar(15),CONVERT(int,@WardID) + 'W-') +
                CONVERT(varchar(6),dbo.fnFormatSerialNoPostfix(1)))

        End

        -- Return the result of the function
        RETURN @RegistrationNo

END

推荐答案

问题出现了，因为你试图调用 dbo.fnRegistrationNoExp 但是你的功能已定义为 [dbo]。[fnRegitrationNoExp] ...您在创建时错误拼写了函数名称（缺少's''）

The problem is happening because you are trying to call dbo.fnRegistrationNoExp but your function has been defined as [dbo].[fnRegitrationNoExp] ... you have misspelled the function name when creating it (missing ''s'')

这篇关于无法找到任何列“dbo”或者用户定义的函数或聚合“dbo.fnRegistrationNoExp”，或者名称不明确。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！