3

I'm comparing two tables that should have the same data. I've got a couple of different queries that are supposed to return rows that don't match. However, the queries return rows that DO match. I'm not sure why. Here is an example:

select 
which = 'TableA',
*
from (
select * from compareTempA
except
select * from compareTempB
) x
union all
select
'TableB',
*
from (
select * from compareTempB
except
select * from compareTempA

) x
order by dateindex, empid, serviceSales, productSales

For some reason I get results like the following:

Query Results

As you can see these rows appear to match. I've also tried:

select sum(case when A.productSales = B.productSales then 0 else 1 end) from 
compareTempA A
join compareTempB B
on A.dateindex = B.dateindex
and A.empid = B.empid
and A.storeid = B.storeid

To which I get 3011. I'm baffled because the data is supposed to be the same in both tables. The only thing I can think of is that I formed the two tables by inserting into each of them in the following way:

insert into compareTempA
select * from someView

And I did the same for compareTempB. I tried truncating the tables first and reinserting. Please help!

  • dateindex column type? Is it INT, CHAR, DATE... DATETIME? – jean May 7 '18 at 13:12
  • dateindex column type is an INT. Is this an issue? – J4ce May 7 '18 at 13:15
  • 3
    What is the primary key of the tables? Can you provide the CREATE TABLE statements? – ypercubeᵀᴹ May 7 '18 at 13:44
3

The only thing that comes to my mind is that you are not using the same data type for the column productSales on both tables.

For example, if your data type on compareTempA is float and on compareTempB is decimal, that's exactly what should happen.

By the way, this happens even if both columns are float, due to the fact that float is not a precise datatype.

I suggest that you change your datatype to a precise one, like decimal.

"Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. The ISO synonym for real is float(24)." Source: https://docs.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-2017

Similar problems:

Same problem here - https://stackoverflow.com/questions/16149966/comparing-2-float-values-in-sqlserver

Best Pratices Red Gate - https://documentation.red-gate.com/codeanalysis/best-practice-rules/bp023

Another question about floats and precise comparisions - https://stackoverflow.com/questions/10737004/should-we-use-float-as-primary-key-in-sql-server

  • same datatype. float for both. – J4ce May 7 '18 at 13:19
  • 1
    Is it possible to use a decimal type? for definition, float is a approximate data type and should not be used for index or precise comparisons – Evandro Muchinski May 7 '18 at 13:25
  • You could also, only on your except, add a cast to decimal(15,4) - for instance - on your float column, instead of using only * – Evandro Muchinski May 7 '18 at 13:44
  • 1
    Yep. That's the issue. I changed the type from float to decimal. Now the first query i mentioned above is returning only one difference. PorductSales of 57 on compareTempB and 58 on compareTempA. I can live with this. Thanks so much! – J4ce May 7 '18 at 13:44
  • @J4ce - you're dealing with monetary values, which should never be stored/processed as (binary) floats, but some sort of base-10 decimal, because that's the "Actual" type of the number. For some issues, see this question. – Clockwork-Muse May 7 '18 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.