以下SQL查询有什么问题 [英] What is wrong in the below SQL query

查看：55
本文介绍了以下SQL查询有什么问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

  SELECT  * 
  FROM  ratecard ratecard  INNER   JOIN  RateCardStagingTable UPDATEDATA  ON  ratecard.ratetableid = < span class =code-sdkkeyword> @ RateTableID  
  WHERE （ratecard.ratecategoryid1 = UPDATEDATA.ratecategoryid1  OR （ratecard.ratecategoryid1  IS   NULL   AND  UPDATEDATA.ratecategoryid1  IS   NULL ））
  AND （ratecard.ratecategoryid2 = UPDATEDATA.ratecategoryid2  OR （ratecard.ratecategoryid2  IS   NULL   AND  UPDATEDATA.ratecategoryid2  IS   NULL ））
  AND （ratecard.ratecategoryid3 = UPDATEDATA.ratecategoryid3  OR （ratecard.ratecategoryid3  IS   NULL   AND  UPDATEDATA.ratecategoryid3  IS   NULL ））
  AND （ratecard.ratecategoryid4 = UPDATEDATA .ratecategoryid4  OR （ratecard.ratecategoryid4  IS   NULL   AND  UPDATEDATA.ratecategoryid4  IS   NULL ） ）
  AND  （ratecard.ratecategoryid5 = UPDATEDATA.ratecategoryid5  OR （ratecard.ratecategoryid5  IS   NULL   AND  UPDATEDATA.ratecategoryid5  IS   NULL ））
  AND （ratecard.ratecategoryid6 = UPDATEDATA.ratecategoryid6  OR （ ratecard.ratecategoryid6  IS   NULL   AND  UPDATEDATA。 ratecategoryid6  IS   NULL ））
  AND （ratecard.ratecategoryid7 = UPDATEDATA.ratecategoryid7  OR （ratecard.ratecategoryid7  IS   NULL   AND  UPDATEDATA.ratecategoryid7  IS   NULL ））
  AND （ratecard.ratecategoryid8 = UPDATEDATA.ratecategoryid8  OR （ratecard.ratecategoryid8  IS   NULL   AND  UPDATEDATA.ratecategoryid8  IS   NULL ））
  AND （ratecard.ratecategoryid9 = UPDATEDATA .ratecategoryid9  OR （ratecard.ratecategoryid9  IS   NULL   AND  UPDATEDATA.ratecategoryid9  IS   NULL ） ）
  AND  （ratecard.ratecategoryid10 = UPDATEDATA.ratecategoryid10  OR （ratecard.ratecategoryid10  IS   NULL   AND  UPDATEDATA.ratecategoryid10  IS   NULL ））
  GROUP   BY  UPDATEDATA.ID，UPDATEDATA.RateCategoryID1，UPDATEDATA .RateCategoryID2，UPDATEDATA.RateCategoryID3，UPDATEDATA.RateCategoryID4，UPDATEDATA.RateCategoryID5，UPDATEDATA.RateCategoryID6，UPDATEDATA.RateCategoryID7，UPDATEDATA.RateCategoryID8，UPDATEDATA.RateCategoryID9，UPDATEDATA.RateCategoryID10，UPDATEDATA.low，UPDATEDATA。 target ，UPDATEDATA.high，UPDATEDATA.CustomFields，UPDATEDATA.DataPointsCount 
 
 
 
  SELECT  *  FROM  
（RateCardStagingTable DI  LEFT   JOIN （
  SELECT  UPDATEDATA .ID，UPDATEDATA.RateCategoryID1，UPDATEDATA.RateCategoryID2，UPDATEDATA.RateCategoryID3，UPDATEDATA.RateCategoryID4，UPDATEDATA.RateCategoryID5，UPDATEDATA.RateCategoryID6，UPDATEDATA.RateCategoryID7，UPDATEDATA.RateCategoryID8，UPDATEDATA.RateCategoryID9，UPDATEDATA.RateCategoryID10，UPDATEDATA.low，UPDATEDATA。< span class =code-keyword> target ，UPDATEDATA.high，UPDATEDATA.CustomFields，UPDATEDATA.DataPointsCount 
  FROM  ratecard ratecard  INNER   JOIN  RateCardStagingTable UPDATEDATA  ON  ratecard.ratetableid =  @ RateTableID  
  WHERE （ratecard.ratecategoryid1 = UPDATEDATA.ratecategoryid1  OR （ratecard.ratecategory id1  IS   NULL   AND  UPDATEDATA.ratecategoryid1 < span class =code-keyword> IS   NULL ））
  AND  （ratecard.ratecategoryid2 = UPDATEDATA.ratecategoryid2  OR （ratecard.ratecategoryid2  IS   NULL   AND  UPDATEDATA.ratecategoryid2  IS   NULL ））
  AND （ratecard.ratecategoryid3 = UPDATEDATA.ratecategoryid3  OR （ ratecard.ratecategoryid3  IS   NULL   AND  UPDATEDATA。 ratecategoryid3  IS   NULL ））
  AND （ratecard.ratecategoryid4 = UPDATEDATA.ratecategoryid4  OR （ratecard.ratecategoryid4  IS   NULL   AND  UPDATEDATA.ratecategoryid4  IS   NULL ））
  AND （ratecard.ratecategoryid5 = UPDATEDATA.ratecategoryid5  OR （ratecard.ratecategoryid5  IS   NULL   AND  UPDATEDATA.ratecategoryid5  IS   NULL ））
  AND （ratecard.ratecategoryid6 = UPDATEDATA.ratecategoryid6  OR （ratecard.ratecategory id6  IS   NULL   AND  UPDATEDATA.ratecategoryid6 < span class =code-keyword> IS   NULL ））
  AND  （ratecard.ratecategoryid7 = UPDATEDATA.ratecategoryid7  OR （ratecard.ratecategoryid7  IS   NULL   AND  UPDATEDATA.ratecategoryid7  IS   NULL ））
  AND （ratecard.ratecategoryid8 = UPDATEDATA.ratecategoryid8  OR （ ratecard.ratecategoryid8  IS   NULL   AND  UPDATEDATA。 ratecategoryid8  IS   NULL ））
  AND （ratecard.ratecategoryid9 = UPDATEDATA.ratecategoryid9  OR （ratecard.ratecategoryid9  IS   NULL   AND  UPDATEDATA.ratecategoryid9  IS   NULL ））
  AND （ratecard.ratecategoryid10 = UPDATEDATA.ratecategoryid10  OR （ratecard.ratecategoryid10  IS   NULL   AND  UPDATEDATA.ratecategoryid10  IS   NULL ））
  GROUP   BY  UPDATEDATA.ID， UPDATEDATA.RateCategoryID1，UPDATEDATA.RateCategoryID2，UPDATEDATA.RateC ategoryID3，UPDATEDATA.RateCategoryID4，UPDATEDATA.RateCategoryID5，UPDATEDATA.RateCategoryID6，UPDATEDATA.RateCategoryID7，UPDATEDATA.RateCategoryID8，UPDATEDATA.RateCategoryID9，UPDATEDATA.RateCategoryID10，UPDATEDATA.low，UPDATEDATA。 target ，UPDATEDATA.high，UPDATEDATA.CustomFields，UPDATEDATA.DataPointsCount）UD  ON  DI.id = UD.id）  -   WHERE UD.id IS NULL  
 
  SET  @ UpdatedRecords =案例 WHEN   @ UpdatedRecords   IS   NULL  那么  0   ELSE   @ UpdatedRecords   END  
  SET  @ InsertedCount = 案例  WHEN   @ InsertedCount   IS   NULL  那么  0  < span class =code-keyword> ELSE   @ InsertedCount   END  
 
 
  SET   @ UpdateRecordsnumber  = @ UpdatedRecords 
  SET   @ InsertedRecordsNumber  = @ InsertedCount





我得到这个错误我可以;弄清楚问题



以下是错误



消息305，级别16，状态1，过程GetImportRateTableDetails，行233 
除非使用IS NULL运算符，否则无法比较或排序XML数据类型。 
消息305，级别16，状态1，过程GetImportRateTableDetails，行251 
除非使用IS NULL运算符，否则无法比较或排序XML数据类型。





我尝试了什么：



我在这个查询中做错了什么

解决方案

我在group by子句中添加xml数据字段遇到了这样的问题。

当然我不推荐使用xml group by子句中的字段。

我克服的方法是将xml字段转换为varchar，让sql进行排序。

通过将GroupBy字段中的xml列转换为以下CONVERT（ VARCHAR（最大），xml_data_field_name）。

SELECT * 
	FROM   ratecard ratecard INNER JOIN RateCardStagingTable UPDATEDATA  ON ratecard.ratetableid = @RateTableID
			WHERE  (  ratecard.ratecategoryid1 = UPDATEDATA.ratecategoryid1 OR (  ratecard.ratecategoryid1 IS NULL AND UPDATEDATA.ratecategoryid1 IS NULL ) ) 
               AND ( ratecard.ratecategoryid2 = UPDATEDATA.ratecategoryid2 OR ( ratecard.ratecategoryid2 IS NULL AND UPDATEDATA.ratecategoryid2 IS NULL ) ) 
               AND ( ratecard.ratecategoryid3 = UPDATEDATA.ratecategoryid3 OR ( ratecard.ratecategoryid3 IS NULL AND UPDATEDATA.ratecategoryid3 IS NULL ) ) 
               AND ( ratecard.ratecategoryid4 = UPDATEDATA.ratecategoryid4 OR ( ratecard.ratecategoryid4 IS NULL AND UPDATEDATA.ratecategoryid4 IS NULL ) ) 
               AND ( ratecard.ratecategoryid5 = UPDATEDATA.ratecategoryid5 OR ( ratecard.ratecategoryid5 IS NULL AND UPDATEDATA.ratecategoryid5 IS NULL ) ) 
               AND ( ratecard.ratecategoryid6 = UPDATEDATA.ratecategoryid6 OR ( ratecard.ratecategoryid6 IS NULL AND UPDATEDATA.ratecategoryid6 IS NULL ) ) 
               AND ( ratecard.ratecategoryid7 = UPDATEDATA.ratecategoryid7 OR ( ratecard.ratecategoryid7 IS NULL AND UPDATEDATA.ratecategoryid7 IS NULL ) ) 
               AND ( ratecard.ratecategoryid8 = UPDATEDATA.ratecategoryid8 OR ( ratecard.ratecategoryid8 IS NULL AND UPDATEDATA.ratecategoryid8 IS NULL ) ) 
               AND ( ratecard.ratecategoryid9 = UPDATEDATA.ratecategoryid9 OR ( ratecard.ratecategoryid9 IS NULL AND UPDATEDATA.ratecategoryid9 IS NULL ) ) 
               AND ( ratecard.ratecategoryid10 = UPDATEDATA.ratecategoryid10 OR ( ratecard.ratecategoryid10 IS NULL  AND UPDATEDATA.ratecategoryid10 IS NULL ) )
		GROUP BY UPDATEDATA.ID , UPDATEDATA.RateCategoryID1,UPDATEDATA.RateCategoryID2,UPDATEDATA.RateCategoryID3,UPDATEDATA.RateCategoryID4,UPDATEDATA.RateCategoryID5,UPDATEDATA.RateCategoryID6,UPDATEDATA.RateCategoryID7,UPDATEDATA.RateCategoryID8,UPDATEDATA.RateCategoryID9,UPDATEDATA.RateCategoryID10,UPDATEDATA.low,UPDATEDATA.target,UPDATEDATA.high,UPDATEDATA.CustomFields,UPDATEDATA.DataPointsCount


			
			SELECT * FROM
			( RateCardStagingTable DI  LEFT JOIN(
		SELECT UPDATEDATA.ID , UPDATEDATA.RateCategoryID1,UPDATEDATA.RateCategoryID2,UPDATEDATA.RateCategoryID3,UPDATEDATA.RateCategoryID4,UPDATEDATA.RateCategoryID5,UPDATEDATA.RateCategoryID6,UPDATEDATA.RateCategoryID7,UPDATEDATA.RateCategoryID8,UPDATEDATA.RateCategoryID9,UPDATEDATA.RateCategoryID10,UPDATEDATA.low,UPDATEDATA.target,UPDATEDATA.high,UPDATEDATA.CustomFields,UPDATEDATA.DataPointsCount
	FROM   ratecard ratecard INNER JOIN RateCardStagingTable UPDATEDATA  ON ratecard.ratetableid = @RateTableID
			WHERE  (  ratecard.ratecategoryid1 = UPDATEDATA.ratecategoryid1 OR (  ratecard.ratecategoryid1 IS NULL AND UPDATEDATA.ratecategoryid1 IS NULL ) ) 
               AND ( ratecard.ratecategoryid2 = UPDATEDATA.ratecategoryid2 OR ( ratecard.ratecategoryid2 IS NULL AND UPDATEDATA.ratecategoryid2 IS NULL ) ) 
               AND ( ratecard.ratecategoryid3 = UPDATEDATA.ratecategoryid3 OR ( ratecard.ratecategoryid3 IS NULL AND UPDATEDATA.ratecategoryid3 IS NULL ) ) 
               AND ( ratecard.ratecategoryid4 = UPDATEDATA.ratecategoryid4 OR ( ratecard.ratecategoryid4 IS NULL AND UPDATEDATA.ratecategoryid4 IS NULL ) ) 
               AND ( ratecard.ratecategoryid5 = UPDATEDATA.ratecategoryid5 OR ( ratecard.ratecategoryid5 IS NULL AND UPDATEDATA.ratecategoryid5 IS NULL ) ) 
               AND ( ratecard.ratecategoryid6 = UPDATEDATA.ratecategoryid6 OR ( ratecard.ratecategoryid6 IS NULL AND UPDATEDATA.ratecategoryid6 IS NULL ) ) 
               AND ( ratecard.ratecategoryid7 = UPDATEDATA.ratecategoryid7 OR ( ratecard.ratecategoryid7 IS NULL AND UPDATEDATA.ratecategoryid7 IS NULL ) ) 
               AND ( ratecard.ratecategoryid8 = UPDATEDATA.ratecategoryid8 OR ( ratecard.ratecategoryid8 IS NULL AND UPDATEDATA.ratecategoryid8 IS NULL ) ) 
               AND ( ratecard.ratecategoryid9 = UPDATEDATA.ratecategoryid9 OR ( ratecard.ratecategoryid9 IS NULL AND UPDATEDATA.ratecategoryid9 IS NULL ) ) 
               AND ( ratecard.ratecategoryid10 = UPDATEDATA.ratecategoryid10 OR ( ratecard.ratecategoryid10 IS NULL  AND UPDATEDATA.ratecategoryid10 IS NULL ) )
		GROUP BY UPDATEDATA.ID , UPDATEDATA.RateCategoryID1,UPDATEDATA.RateCategoryID2,UPDATEDATA.RateCategoryID3,UPDATEDATA.RateCategoryID4,UPDATEDATA.RateCategoryID5,UPDATEDATA.RateCategoryID6,UPDATEDATA.RateCategoryID7,UPDATEDATA.RateCategoryID8,UPDATEDATA.RateCategoryID9,UPDATEDATA.RateCategoryID10,UPDATEDATA.low,UPDATEDATA.target,UPDATEDATA.high,UPDATEDATA.CustomFields,UPDATEDATA.DataPointsCount )UD ON DI.id=UD.id)-- WHERE  UD.id IS NULL
	
SET @UpdatedRecords=Case WHEN @UpdatedRecords IS NULL THEN 0 ELSE @UpdatedRecords END
SET @InsertedCount= Case WHEN @InsertedCount IS NULL THEN 0 ELSE @InsertedCount END

	
	SET @UpdateRecordsnumber =@UpdatedRecords
	SET  @InsertedRecordsNumber =@InsertedCount



I am getting this error i can;t figure out the problem

below is the error 

Msg 305, Level 16, State 1, Procedure GetImportRateTableDetails, Line 233
The XML data type cannot be compared or sorted, except when using the IS NULL operator.
Msg 305, Level 16, State 1, Procedure GetImportRateTableDetails, Line 251
The XML data type cannot be compared or sorted, except when using the IS NULL operator.



What I have tried:

What is wrong i am doing in this query

解决方案

I have come across such problem in adding xml data fields in group by clause.
Of course I don't recommend using xml fields in group by clause.
The way I overcome is by converting the xml field to varchar that way letting sql to sort.
Try following by converting the xml column in the GroupBy field as following CONVERT(VARCHAR(max),xml_data_field_name).


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

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆