解决此查询错误 [英] Solve this query error

问题描述

 开始 
 声明  @ day   int  
  set   @ day  =  2  
 
 声明  @ days   varchar （ 8000 ）
  set   @ days  = ' < span class =code-string> [1]' 
   -   声明最终专栏Pivot的名称 
 声明  @ columnsday   NVARCHAR （MAX）
  set   @ colu mnsday  = '  isnull（cast（[1] as varchar（10）），''''） as [1]' 
  WHILE （ @ day < =  31 ）
  BEGIN  
  set   @ days  =  @ days  + ' ，' + quotename（ convert （ varchar ， @ day ））
  set   @ columnsday  =  @ columnsday  + ' ，ISNULL （cast（' + quotename（ convert （ varchar ， @ day ））+ '  as varchar（10）），' '''）' + quotename（ convert （ varchar ， @ day ））
  set   @ day  = < span class =code-sdkkeyword> @ day  +  1  
   -    print @days  
  END  
   -    print @days  
   -    print @columnsday  
 
  DECLARE   @ query   varchar （ 8000 ）
  set   @ query  = '  SELECT StudentCurrId，
'  +  @ columnsday  + ' 来自
（SELECT tbl_StudentAttendanceDetails.StuAttendId，tbl_StudentAttendanceDetails.StudentCurrId，
 StudentCode，upper（First_Name +'''+ Middle_Name +''''+ Last_Name）as Stuname，
 day（StudentCurrId）as day， Stuattend FROM tbl_StudentAttendanceDetails 
内加入
 tbl_StudentAttendanceMaster $ b $上tbl_StudentAttendanceDetails.StuAttendId = tbl_StudentAttendanceMaster.StuAttendId $ b b $ b内加入tbl_Student_Current 
上tbl_StudentAttendanceDetails.StudentCurrId = tbl_Student_Current.StuCurrentId 
内加入关于tbl_Student_Current.StudentId = StudentAdmission.StudentId 
的StudentAdmission，其中tbl_StudentAttendanceMaster.SessionId =' +18+ '  
）AS SourceTable 
 
 pivot（
 MAX（Stuattend）
 for （' +  @ days  + ' ）
）p' 
 
  print （' 透视报告'）
  exec （ @query ）
  end  

给出错误

=====================

消息 245 ，等级 16 ，状态 1 ，行 21  
转换失败 转换 nvarchar 值'  SELECT StudentCurrId，
 isnull（cast（[1] as varchar（10）），''）as [1]，ISNULL （cast（[2] as varchar（10）），''）as [2]，ISNULL（cast（[3] as varchar（10）），''）as [3]，ISNULL（cast（[4] as varchar（10）），''）as [4]，ISNULL（cast（[5] as varchar（10）），''）as [5]，ISNULL（cast（[6] as varchar（10）） ，''）[6]，ISNULL（强制转换（[7]为varchar（10）），''）为[7]，ISNULL（强制转换（[8]为varchar（10）），''）为[ 8]，ISNULL（强制转换（[9]为varchar（10）），''）为[9]，ISNULL（强制转换（[10]为varchar（10）），''）为[10]，ISNULL（强制转换） （[11] as varchar（10）），''）as [11]，ISNULL（cast（[12] as varchar（10）），''）as [12]，ISNULL（cast（[13] as varchar （10）），''）as [13]，ISNULL（cast（[14] as varchar（10）），''）as [14]，ISNULL（cast（[15] as varchar（10）），' '）as [15]，ISNULL（cast（[16] as varchar（10）），''）as [16]，ISNULL（cast（[17] as varchar（10）），''）as [17] ，ISNULL（演员（[18]为varchar（10）），''）为[18]，ISNULL（演员（[19]为varchar（10）），''）为[19]，ISNULL（演员阵容[ 20] as varchar（10）），''）as [20]，ISNULL（cast（[21] as varchar（ 10）），''）as [21]，ISNULL（cast（[22] as varchar（10）），''）as [22]，ISNULL（cast（[23] as varchar（10）），'' ）如[23]，ISNULL（演员（[24] as varchar（10）），''）[24]，ISNULL（演员（[25] as varchar（10）），''）[25]， ISNULL（演员（[26]为varchar（10）），''）为[26]，ISNULL（演员（[27]为varchar（10）），''）为[27]，ISNULL（演员（[28] ] as varchar（10）），''）as [28]，ISNULL（cast（[29] as varchar（10）），''）as [29]，ISNULL（cast（[30] as varchar（10） ），''）如[30]，ISNULL（演员（[31] as varchar（10）），''）[31]来自
（SELECT tbl_StudentAttendanceDetails.StuAttendId，tbl_StudentAttendanceDetails.StudentCurrId，
 StudentCode，upper（First_Name +' '  + Middle_Name +' '  + Last_Name）as Stuname，
 day（StudentCurrId）as day，Stuattend FROM tbl_StudentAttendanceDetails 
 inner join 
 tbl_StudentAttendanceMaster 
 on tbl_StudentAttendanceDetails.StuAttendId = tbl_StudentAttendanceMaster.StuAttendId 
内部联接tbl_Student_Current 
 on tbl_StudentAttendanceDetails.StudentCurrId = tbl_Student_Current.StuCurrentId 
内部联接StudentAdmission ...  

解决方案

请执行此操作并检查它是否有效？



开始

声明@day int

set @day = 2



声明@days varchar（8000）

set @days ='[ 1]'

- 声明Pivot的最终列名

声明@columnsday NVARCHAR（MAX）

set @columnsday ='isnull（cast （[1] as varchar（10）），''''）[1]'

WHILE（@day< = 31）

BEGIN

设置@days = @days +'，'+ quotename（convert（varchar，@ day））

set @columnsday = @columnday +'，ISNULL（cast（'+ quotename （将（varchar，@ day））+'as varchar（10）），''''）转换为'+ quotename（转换） （varchar，@ day））

设置@day = @day + 1

--print @days

END



DECLARE @query varchar（8000）

set @query ='SELECT StudentCurrId，

'+ @columnsday +'from

（SELECT tbl_StudentAttendanceDetails.StuAttendId，tbl_StudentAttendanceDetails.StudentCurrId，

StudentCode，upper（First_Name +'''+ Middle_Name +''''+ Last_Name）as stuname，

天（StudentCurrId）当天，Stuattend FROM tbl_StudentAttendanceDetails

内部联接

tbl_StudentAttendanceMaster

on tbl_StudentAttendanceDetails.StuAttendId = tbl_StudentAttendanceMaster。 StuAttendId

内部联接tbl_Student_Current

on tbl_StudentAttendanceDetails.StudentCurrId = tbl_Student_Current.StuCurrentId

内部联接学生入学tbl_Student_Current.StudentId = StudentAdmission.StudentId

其中tbl_StudentAttendanceMaster.SessionId = 18'+ +'

）AS SourceTable



pivot（

MAX（Stuattend） ）
每天
（'+ @days +'）

）p'



print（'转发报告'）

- 打印（@query）

exec（@query）

结束

< blockquote>我们无法帮助您，因为我们无法访问您的数据库。唯一可以解决这个错误的人是你！

我建议更换这个：

  print （'  pivoted report'）
  exec （ @ query ）

with

  print （ @ query ）
   -    exec（@query） 

看什么 @query 变量成立。然后你就能解决问题了。

begin
   declare @day  int
   set @day = 2

   declare @days varchar(8000)
   set @days = '[1]'
   -- Declare final column names for Pivot
   Declare @columnsday NVARCHAR(MAX)
   set @columnsday = 'isnull(cast([1] as varchar(10)),'''') as [1]'
   WHILE (@day <= 31)
   BEGIN
       set @days = @days + ', ' + quotename(convert(varchar, @day))
       set @columnsday = @columnsday + ',ISNULL(cast(' + quotename(convert(varchar, @day)) + ' as varchar(10)) ,'''') as '+ quotename(convert(varchar, @day))
       set @day  = @day + 1
       --print @days
   END
   --print @days
   --print @columnsday

   DECLARE @query varchar(8000)
   set @query = 'SELECT StudentCurrId,
' + @columnsday  + 'from
     (SELECT tbl_StudentAttendanceDetails.StuAttendId,tbl_StudentAttendanceDetails.StudentCurrId,
     StudentCode,upper(First_Name + '' '' + Middle_Name + '' '' + Last_Name) as Stuname,
     day(StudentCurrId) as day,Stuattend FROM tbl_StudentAttendanceDetails
     inner join
     tbl_StudentAttendanceMaster
     on tbl_StudentAttendanceDetails.StuAttendId=tbl_StudentAttendanceMaster.StuAttendId
   inner join tbl_Student_Current
   on tbl_StudentAttendanceDetails.StudentCurrId=tbl_Student_Current.StuCurrentId
   inner join StudentAdmission on tbl_Student_Current.StudentId=StudentAdmission.StudentId
          where  tbl_StudentAttendanceMaster.SessionId='+18+'
         ) AS SourceTable

      pivot (
            MAX(Stuattend)
               for day in (' + @days +')
             )p'

 print('pivoted report')
exec(@query)
end

error given
=====================

Msg 245, Level 16, State 1, Line 21
Conversion failed when converting the nvarchar value 'SELECT StudentCurrId,
isnull(cast([1] as varchar(10)),'') as [1],ISNULL(cast([2] as varchar(10)) ,'') as [2],ISNULL(cast([3] as varchar(10)) ,'') as [3],ISNULL(cast([4] as varchar(10)) ,'') as [4],ISNULL(cast([5] as varchar(10)) ,'') as [5],ISNULL(cast([6] as varchar(10)) ,'') as [6],ISNULL(cast([7] as varchar(10)) ,'') as [7],ISNULL(cast([8] as varchar(10)) ,'') as [8],ISNULL(cast([9] as varchar(10)) ,'') as [9],ISNULL(cast([10] as varchar(10)) ,'') as [10],ISNULL(cast([11] as varchar(10)) ,'') as [11],ISNULL(cast([12] as varchar(10)) ,'') as [12],ISNULL(cast([13] as varchar(10)) ,'') as [13],ISNULL(cast([14] as varchar(10)) ,'') as [14],ISNULL(cast([15] as varchar(10)) ,'') as [15],ISNULL(cast([16] as varchar(10)) ,'') as [16],ISNULL(cast([17] as varchar(10)) ,'') as [17],ISNULL(cast([18] as varchar(10)) ,'') as [18],ISNULL(cast([19] as varchar(10)) ,'') as [19],ISNULL(cast([20] as varchar(10)) ,'') as [20],ISNULL(cast([21] as varchar(10)) ,'') as [21],ISNULL(cast([22] as varchar(10)) ,'') as [22],ISNULL(cast([23] as varchar(10)) ,'') as [23],ISNULL(cast([24] as varchar(10)) ,'') as [24],ISNULL(cast([25] as varchar(10)) ,'') as [25],ISNULL(cast([26] as varchar(10)) ,'') as [26],ISNULL(cast([27] as varchar(10)) ,'') as [27],ISNULL(cast([28] as varchar(10)) ,'') as [28],ISNULL(cast([29] as varchar(10)) ,'') as [29],ISNULL(cast([30] as varchar(10)) ,'') as [30],ISNULL(cast([31] as varchar(10)) ,'') as [31]from
     (SELECT tbl_StudentAttendanceDetails.StuAttendId,tbl_StudentAttendanceDetails.StudentCurrId,
     StudentCode,upper(First_Name + ' ' + Middle_Name + ' ' + Last_Name) as Stuname,
     day(StudentCurrId) as day,Stuattend FROM tbl_StudentAttendanceDetails
     inner join
     tbl_StudentAttendanceMaster
     on tbl_StudentAttendanceDetails.StuAttendId=tbl_StudentAttendanceMaster.StuAttendId
   inner join tbl_Student_Current
   on tbl_StudentAttendanceDetails.StudentCurrId=tbl_Student_Current.StuCurrentId
   inner join StudentAdmission...

解决方案

Can you please execute this and check if it works?

begin
declare @day int
set @day = 2

declare @days varchar(8000)
set @days = '[1]'
-- Declare final column names for Pivot
Declare @columnsday NVARCHAR(MAX)
set @columnsday = 'isnull(cast([1] as varchar(10)),'''') as [1]'
WHILE (@day <= 31)
BEGIN
set @days = @days + ', ' + quotename(convert(varchar, @day))
set @columnsday = @columnsday + ',ISNULL(cast(' + quotename(convert(varchar, @day)) + ' as varchar(10)) ,'''') as '+ quotename(convert(varchar, @day))
set @day = @day + 1
--print @days
END

DECLARE @query varchar(8000)
set @query = 'SELECT StudentCurrId,
' + @columnsday + 'from
(SELECT tbl_StudentAttendanceDetails.StuAttendId,tbl_StudentAttendanceDetails.StudentCurrId,
StudentCode,upper(First_Name + '' '' + Middle_Name + '' '' + Last_Name) as Stuname,
day(StudentCurrId) as day,Stuattend FROM tbl_StudentAttendanceDetails
inner join
tbl_StudentAttendanceMaster
on tbl_StudentAttendanceDetails.StuAttendId=tbl_StudentAttendanceMaster.StuAttendId
inner join tbl_Student_Current
on tbl_StudentAttendanceDetails.StudentCurrId=tbl_Student_Current.StuCurrentId
inner join StudentAdmission on tbl_Student_Current.StudentId=StudentAdmission.StudentId
where tbl_StudentAttendanceMaster.SessionId=18'+ + '
) AS SourceTable

pivot (
MAX(Stuattend)
for day in (' + @days +')
)p'

print('pivoted report')
-- print(@query)
exec(@query)
end

We can't help you, because we don't have access to your database. The only person who can solve this error is You!
I suggest to replace this:

print('pivoted report')
exec(@query)

with

print(@query)
--exec(@query)

to see what @query variable holds. Then you'll be able to resolve your problem.

这篇关于解决此查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！