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 @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)
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 @RegistrationNo
END