SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


EXCEPT returning matching rows


EXCEPT returning matching rows

Author
Message
cpeck
cpeck
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 77
Hi, I made a change to a query to return data quicker. I used the EXCEPT operator to make sure all the data in the old query still matches all the data in the new query and it returned 52 rows. I queried a row from the old query and the new query and put them into Excel (as text) to see what the difference was and they are exactly the same. Then I did a UNION between the old and new queries for one of the rows of data and it only returned one row so they do match. I then did a UNION ALL and it returned 2 rows as expected. What am I missing? Why is the EXCEPT returning matching rows? Shouldn't it only return rows where something does not match? Thanks!
Lowell
Lowell
SSC Guru
SSC Guru (289K reputation)SSC Guru (289K reputation)SSC Guru (289K reputation)SSC Guru (289K reputation)SSC Guru (289K reputation)SSC Guru (289K reputation)SSC Guru (289K reputation)SSC Guru (289K reputation)

Group: General Forum Members
Points: 289625 Visits: 41989
excel changes your data to what it thinks is best, it does not 100% maintain actual values.
does your data have any FLOAT or REAL data types? since those are approximations, they might not be equal
Maybe you need to EXCEPT on a subset of columns, and not all columns?
I'm thinking that something like an identity column, or DWCreatedDate/DWUpdatedDate might be different, but the data is the same?

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
cpeck
cpeck
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 77
So if there was a FLOAT data type, SQL would view the number one way for EXCEPT another way for UNION? There is no update date or anything (and if there were the UNION would have returned more than one row).

cpeck
cpeck
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 77
(Deleted)

cpeck
cpeck
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 77
SOLVED! I was using Row_Number in one of the fields. The way it was sorted wasn't fully defined so SQL was sometimes numbering it one way and sometimes another way.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search