请任何人找到答案 - （列名或提供的值的数量与表定义不匹配） [英] pls anyone findout the answer -(Column name or number of supplied values does not match table definition)

问题描述

USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[func2]    Script Date: 12/24/2013 17:35:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[func2]
(@mdvn int,@fdate date,@tdate date)
returns @funcurz2 table
(
mdv int, fdate date, tdate date,ANC int,
TT1 int,IFA int,BP int,HB int
)
as
begin
declare @vdate date
declare @tvdate date
declare @fedddate date
declare @tedddate date
set @vdate=dateadd(dd,-91,@fdate)
set @tvdate=dateadd(dd,36,@tdate)
set @fedddate=dateadd(dd,20,@fdate)
set @tedddate=dateadd(dd,310,@tdate)
declare @mdv int
declare @cfdate date
declare @ctdate date
declare @ANC int
declare @TT1 int
--declare @TT2 int
declare @IFA int
declare @BP int
declare @HB int
declare funcurz cursor static for
select dvn_cd, sum(REGDT) as Regdt,SUM(ANC) as ANC,SUM(TT1) as TT1, SUM(TT2)as TT2,
SUM(IFA) as IFA, SUM(BP1) as BP1, SUM(HB1) as HB1 from
(
	select dvn_cd,phc_cd,hsc_cd,Visit_Date,TTDate,anc_fullId,TTB,IFADate,BP,HB,
	case when ANEDD IS null then 0 else 1 end as REGDT,
	case when visit_no=3 and ANEDD between @vdate and @tvdate then 1 else 0  end as ANC, 	 
	case when TTB=1 and ANEDD between @fdate and @tdate then 1 else 0  end as TT1, 	 
	case when TTB>2 and ANEDD between @fdate and @tdate then 1 else 0  end as TT2, 	 
	case when IFA=100 and ANEDD between @fdate and @tdate then 1 else 0  end as IFA, 	 
	case when BP>='140/90' and ANEDD between @fdate and @tdate then 1 else 0  end as BP1,
	case when HB<11 and ANEDD between @fdate and @tdate then 1 else 0  end as HB1
	from ANVisits3 a where DVN_CD=@mdvn and ANEDD between @fedddate and @tedddate
)a group by dvn_cd,phc_cd,hsc_cd
open funcurz
begin
	fetch next from funcurz into @mdv,@cfdate,@ctdate,@ANC,@TT1,@IFA,@BP,@HB 
		while @@FETCH_STATUS=0
		   begin
			insert into @funcurz2--(@mdv,@cfdate,@ctdate,@ANC,@TT1,@TT2,@IFA,@BP,@HB)
			select 'DVN : '+ @mdvn +',FDT :'+convert(varchar(20),@fdate)+',TDT :'+convert(varchar(20),@tdate)+',ANC :'+@ANC+',TT1 :'+@TT1+
				',IFA :'+@IFA+	',BP :'+@BP+',HB:'+ @HB  
   			fetch next from funcurz into @mdv,@cfdate,@ctdate,@ANC,@TT1,@IFA,@BP,@HB 
			END
end
close funcurz
deallocate funcurz
return  
end

Msg 213, Level 16, State 1, Procedure func2, Line 46
Column name or number of supplied values does not match table definition.

推荐答案

USE [master]
GO
/****** Object: UserDefinedFunction [dbo].[func2] Script Date: 12/24/2013 17:35:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[func2]
(@mdvn int,@fdate date,@tdate date)
returns @funcurz2 table
(
mdv int, fdate date, tdate date,ANC int,
TT1 int,IFA int,BP int,HB int
)
as
begin
declare @vdate date
declare @tvdate date
declare @fedddate date
declare @tedddate date
set @vdate=dateadd(dd,-91,@fdate)
set @tvdate=dateadd(dd,36,@tdate)
set @fedddate=dateadd(dd,20,@fdate)
set @tedddate=dateadd(dd,310,@tdate)
declare @mdv int
declare @cfdate date
declare @ctdate date
declare @ANC int
declare @TT1 int
--declare @TT2 int
declare @IFA int
declare @BP int
declare @HB int
declare funcurz cursor static for
select dvn_cd, sum(REGDT) as Regdt,SUM(ANC) as ANC,SUM(TT1) as TT1, SUM(TT2)as TT2,
SUM(IFA) as IFA, SUM(BP1) as BP1, SUM(HB1) as HB1 from
(
select dvn_cd,phc_cd,hsc_cd,Visit_Date,TTDate,anc_fullId,TTB,IFADate,BP,HB,
case when ANEDD IS null then 0 else 1 end as REGDT,
case when visit_no=3 and ANEDD between @vdate and @tvdate then 1 else 0 end as ANC, 
case when TTB=1 and ANEDD between @fdate and @tdate then 1 else 0 end as TT1, 
case when TTB>2 and ANEDD between @fdate and @tdate then 1 else 0 end as TT2, 
case when IFA=100 and ANEDD between @fdate and @tdate then 1 else 0 end as IFA, 
case when BP>='140/90' and ANEDD between @fdate and @tdate then 1 else 0 end as BP1,
case when HB<11 and ANEDD between @fdate and @tdate then 1 else 0 end as HB1
from ANVisits3 a where DVN_CD=@mdvn and ANEDD between @fedddate and @tedddate
)a group by dvn_cd,phc_cd,hsc_cd
open funcurz
begin
fetch next from funcurz into @mdv,@ANC,@TT1,@IFA,@BP,@HB 
while @@FETCH_STATUS=0
begin
insert into @funcurz2(@mdv,@cfdate,@ctdate,@ANC,@TT1,@TT2,@IFA,@BP,@HB)
--select 'DVN : '+ @mdvn +',FDT :'+convert(varchar(20),@fdate)+',TDT :'+convert(varchar(20),@tdate)+',ANC :'+@ANC+',TT1 :'+@TT1+
--',IFA :'+@IFA+	',BP :'+@BP+',HB:'+ @HB 
fetch next from funcurz into @mdv,@ANC,@TT1,@IFA,@BP,@HB 
END
end
close funcurz
deallocate funcurz
return 
end


-----what was the actual format and how you want to display

您的游标声明之间存在差异并且获取

即

You have a discrepancy between your cursor declaration and the fetch
i.e. the

declare funcurz cursor static for select ...etc

和

next from funcurz into @mdv,@cfdate,@ctdate,@ANC,@TT1,@IFA,@BP,@HB 

不匹配



目前的映射是

don't match up

The mapping currently is

dvn_cd --> @mdv
sum(REGDT) as Regdt --> @cfdate
SUM(ANC) as ANC --> @ctdate
SUM(TT1) as TT1 --> @ANC
SUM(TT2)as TT2 --> @TT1
SUM(IFA) as IFA --> @IFA
SUM(BP1) as BP1 --> @BP
SUM(HB1) as HB1 --> @HB

但您已将@cfdate和@ctdate定义为 date - 似乎没有使用 SUM



接下来查看表funcurz2的架构，并检查您尝试插入的值的类型是否与您的列类型相匹配表按照它们的定义顺序。如果要以不同的顺序添加它们，或者如果该表中的列多于insert语句中的列，则需要定义要插入的列

but you've defined @cfdate and @ctdate as date - doesn't seem right to be using SUM

Next look at your schema for table funcurz2 and check that the types of values that you are trying to insert match the column types in your table in the order they are defined. If you are adding them in a different order OR if there are more columns in that table than are in your insert statement then you need to define which columns you are inserting

这篇关于请任何人找到答案 - （列名或提供的值的数量与表定义不匹配）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！