0

I have two tables, A & B.

Table A has a column called Nominal which is a float.

Table B has a column called Units which is also a float.

I have a simple select query that highlights any differences between Nominals in table A & Units in table B.

 select coalesce(A.Id, B.Id) Id, A.Nominal, B.Units, isnull(A.Nominal, 0) - isnull(B.Units, 0) Diff
 from tblA A full outer join tblB B
 on tblA.Id = tblB.Id
 where isnull(A.Nominal, 0) - isnull(B.Units, 0) <> 0

this query works. However this morning I have a slight problem.

The query is showing on line as having a difference,

   Id    Nominal      Units     Diff
   FJLK  100000       100000    1.4515E-11

So obviously one or both of the figures are not 100,000 exactly. However when I run a select query on both tables (individually) on this id both of them return 100,000 I can't see which one has decimal places, why is this? Is this some sort of default display in SQL Server?

mHelpMe
  • 6,336
  • 24
  • 75
  • 150
  • 1
    This may help: http://stackoverflow.com/questions/1056323/difference-between-numeric-float-and-decimal-in-sql-server, which is _decimal vs float_ – Prisoner Jan 24 '17 at 09:00
  • May be a front-end code problem.. They need to capture the float value with sufficient size and display – Shakeer Mirza Jan 24 '17 at 09:00

1 Answers1

0

In the excel you will find this kind of behavior.

It's a standard way to represent a low numbers. The number 1.4515E-11 you got is same 1.4515 * 10^(-11)

Shakeer Mirza
  • 5,054
  • 2
  • 18
  • 41