SQL查询中的错误是什么 [英] what is the Error in sql query

问题描述

大家好，

请告诉我下面的SQL查询中有什么错误..

hello everyone,

Please tell me what is the error in below sql query..

declare @userid int=null
declare @complaintstatusid int=null
declare @fromdate date=null
declare @todate date =null
declare @month date=null



DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = 
  COALESCE(
    @PivotColumnHeaders + '',['' + cast(AssetType as varchar) + '']'',
   ''['' + cast(AssetType as varchar)+ '']''
  )
FROM [MH.AssetType_Master] A where isdeleted =''false''
--select @PivotColumnHeaders 

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N''
select * from 
(
select S.SchoolName, A.AssetType, S.SchoolId,um.Username +'''' ''''+um.Surname as Name,csm.ComplaintStatus
from [MH.Complaints] C 
inner join [Mh.AssetType_Master] A 
on A.AssetTypeId = C.AssetTypeID 
inner join [Mh.school_Master] S
 on S.SchoolId = C.SchoolID
 inner join [MH.UserSchoolAssociation] usa
 on S.SchoolId=usa.SchoolId
 inner join [MH.User_Master] um
 on usa.UserId=um.LoginId
 inner join [MH.ComplaintStatus_Master] csm
 on  C.ComplaintStatusId=csm.ComplaintStatusId
 
 
where Month(c.Reporteddate)= coalesce(''+CONVERT(nvarchar(max),Month(@month))+'',Month(c.Reporteddate))


) PivotData
Pivot
(
  COUNT(SchoolId)
    FOR AssetType IN (
      '' + @PivotColumnHeaders + ''
    )
  ) AS PivotTable
''
select @PivotTableSQL 
--EXECUTE(@PivotTableSQL)

当我选择@pivottablesql查询时，它返回我NULL

请帮帮我..


问候，
Krunal

when i select @pivottablesql query it returns me NULL

Please help me ..


Regards,
Krunal

推荐答案

你好

检查下面编写的代码行

Hello

check below code line which is written

where Month(c.Reporteddate)= coalesce('+CONVERT(nvarchar(max),Month(@month))+ ',Month(c.Reporteddate))

在上面的代码中，您正在使用@month变量，然后将变量定义为null，如下所示，并且如果将值分配给@month变量，则永远不会分配该变量.

in above code you are using @month variable and you define then variable as a null like as below and you never assign that variable if you assigned value to @month variable it will work fine

declare @month date=null 

例子

Example

where Month(c.Reporteddate)= coalesce('+CONVERT(nvarchar(max),Month(10))+',Month(c.Reporteddate))

您可以使用Isnull关键字，可以决定如何决定是否出现null值.

You can use Isnull keyword, You can decide what to decide if null value comes.

isnull(CONVERT(nvarchar(max),Month(@month)),'')

在上面的代码中，我只是传递了空字符串('''')如果它为null.
是

In the above code, i just pass empty string('''') if its null.
am

declare @userid int=null
declare @complaintstatusid int=null
declare @fromdate date=null
declare @todate date =null
declare @month date=null



DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
  COALESCE(
    @PivotColumnHeaders + ',[' + cast(Name as varchar) + ']',
   '[' + cast(Name as varchar)+ ']'
  )
FROM employee A
--select @PivotColumnHeaders


DECLARE @PivotTableSQL NVARCHAR(MAX)
select @PivotTableSQL = 'select * from
(
select S.SchoolName, A.AssetType, S.SchoolId,um.Username +'' ''+um.Surname as Name,csm.ComplaintStatus
from [MH.Complaints] C
inner join [Mh.AssetType_Master] A
on A.AssetTypeId = C.AssetTypeID
inner join [Mh.school_Master] S
 on S.SchoolId = C.SchoolID
 inner join [MH.UserSchoolAssociation] usa
 on S.SchoolId=usa.SchoolId
 inner join [MH.User_Master] um
 on usa.UserId=um.LoginId
 inner join [MH.ComplaintStatus_Master] csm
 on  C.ComplaintStatusId=csm.ComplaintStatusId


where Month(c.Reporteddate)= coalesce('+isnull(CONVERT(nvarchar(max),Month(@month)),'null')+',Month(c.Reporteddate))


) PivotData
Pivot
(
  COUNT(SchoolId)
    FOR AssetType IN (
      ' + @PivotColumnHeaders + '
    )
  ) AS PivotTable
'
select @PivotTableSQL

@month为null，不包含where子句.因此，如果您未通过任何月份，这将获取所有记录

check this, if @month is null, am excluding where clause..So this will fetch you all records if you dont pass any month

declare @userid int=null
declare @complaintstatusid int=null
declare @fromdate date=null
declare @todate date =null
declare @month date=null



DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
  COALESCE(
    @PivotColumnHeaders + ',[' + cast(AssetType as varchar) + ']',
   '[' + cast(AssetType as varchar)+ ']'
  )
FROM AssetType_master A
--select @PivotColumnHeaders


DECLARE @PivotTableSQL NVARCHAR(MAX)
select @PivotTableSQL = 'select * from
(
select S.SchoolName, A.AssetType, S.SchoolId,um.Username +'' ''+um.Surname as Name,csm.ComplaintStatus
from [MH.Complaints] C
inner join [Mh.AssetType_Master] A
on A.AssetTypeId = C.AssetTypeID
inner join [Mh.school_Master] S
 on S.SchoolId = C.SchoolID
 inner join [MH.UserSchoolAssociation] usa
 on S.SchoolId=usa.SchoolId
 inner join [MH.User_Master] um
 on usa.UserId=um.LoginId
 inner join [MH.ComplaintStatus_Master] csm
 on  C.ComplaintStatusId=csm.ComplaintStatusId'
 if CONVERT(nvarchar(max),Month(@month)) is not null
 select @PivotTableSQL=@PivotTableSQL+' where Month(c.Reporteddate)= coalesce('+CONVERT(nvarchar(max),Month(@month))+',Month(c.Reporteddate))'

set @PivotTableSQL=@PivotTableSQL+') PivotData
Pivot
(
  COUNT(SchoolId)
    FOR AssetType IN (
      ' + @PivotColumnHeaders + '
    )
  ) AS PivotTable
'
print @PivotTableSQL

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