Never compare two "double precision" numerical numbers directly!

Very Tricky when comparing two numerical numbers in SAS/SQL/Netezza

Most of time we may believe the results should be: equal='

Why?

All numerical numbers are actually stored in floating-point representation. Due to the dumb computer binary(0/1) expression, 2 is expressed as 0000001, since 2^1=2; 0.75 is expressed as 00000.11 since 0.75=2^(-1)+2^(-2). The following is another tricky example.

/***************************************************/

proc sql;

create table Dataout1 as

select *

from data1 as a left join data2 as b

on a.num_var1=b.num_var2

;quit;

/***************************************************/

proc sql;

create table Dataout2 as

select *

from data1 as a left join data2 as b

on abs(a.num_var1-b.num_var2)< 0.01

;quit;

The results from those output datasets might not be the same! The more tricky part is that, the 1st SQL takes much longer than the 2nd SQL. Little Dumb Doctor used to do a similar task in Netezza SQL, it took 30 seconds for 2nd SQL, and it took more than 2 hours from the 1st SQL! More than 2 hours! I'll be very scared to tell others that I'm using netezza to do SQL and It takes more than 2 hours!

Another way to get around this time-consuming task is using integers; sometimes in SQL/Netezza database, some integers will be expressed as double precision numerical values. If we write code to compare those two double precision numerical variables directly, then we will blow up! Waiting hours for the results! Instead, you can use some function e.g.

1) Use integer data type wherever possible(smallint,bigint) instead of floating point.

2) Avoiding floating point wherever possible(real, double precision).

3) Consider Char(N) instead of Varchar(N).

4) Avoid Number --> Character Conversion.