Post reply

EXCEPT returning matching rows

  • June 29, 2017 at 7:59 am

    #326967

    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!

  • June 29, 2017 at 8:05 am

    #1948738

    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!

  • June 29, 2017 at 8:13 am

    #1948741

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

  • June 29, 2017 at 8:14 am

    #1948742

    (Deleted)

  • June 29, 2017 at 10:35 am

    #1948784

    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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply