本文介绍了无法找到任何列“dbo”或者用户定义的函数或聚合“dbo.fnRegistrationNoExp”,或者名称不明确。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在这里,我生成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”或者用户定义的函数或聚合“dbo.fnRegistrationNoExp”,或者名称不明确。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

11-03 03:19