What is wrong in the below SQL query

Question

1.00/5 (1 vote)

See more:

SQL

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

Posted 15-Jun-16 18:01pm

murkalkiran

Updated 20-Jun-16 0:51am

PIEBALDconsult

v3

Add a Solution

Comments

PIEBALDconsult 16-Jun-16 0:49am

Find out which values are XML.

Alex M.H. 17-Jun-16 3:40am

I don't think that theres a problem with the SQL-statement. I guess it's in parsing the delivered dataitems or in the mapping of the the table's structure. Which language do you use? What is 'GetImportRateTableDetails' ?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rocker-Star · Answer 1 · 2016-06-20T00:51:00

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).