Comparing truncated numbers query returning some odd results

Databases
1

Comparing truncated numbers query returning some odd results. I’m sure it’s my query (go figure :rofl:)…

I have two tables that hold similar information (don’t ask me why, this is a situation I inherited), latitude and longitude.

One table carries out the number out to 6 decimal places, and the other out to 4 decimal places. And both tables have an ID field that is the same, a zip code.

I’m attempting to compare the table fields of lat and lon to see if the number before the decimal and the first 2 digits after the decimal are the same (those that follow are not important for this assignment).

The following is the query that does not work, in that it does not return the expected results. I have even changed on record that I know would show up when the query is properly written.

 SELECT t.zip, t.lon AS trunc_lon, e.lon AS ext_lon, t.lat AS trunc_lat, e.lat AS ext_lat
  FROM zip_trunc AS t, zip_ext AS e
  WHERE
  (
    FORMAT(t.lat, 2) != FORMAT(e.lat, 2)
    OR FORMAT(t.lon, 2) != FORMAT(e.lon, 2)
  )
  AND t.zip = e.zip

Here are some of the results:

[TABLE=“class: data ajax”]
[TR]
[/TR]
[TR=“class: odd marked”]
[TD=“class: inline_edit not_null condition”]00501
[/TD]
[TD=“class: inline_edit not_null nowrap, align: right”]-75.0472[/TD]
[TD=“class: inline_edit not_null nowrap, align: right”]-73.046388[/TD]
[TD=“class: inline_edit not_null nowrap, align: right”]40.8144[/TD]
[TD=“class: inline_edit not_null nowrap, align: right”]40.813078[/TD]
[/TR]
[TR=“class: even”]
[TD=“class: inline_edit not_null condition”]00501
[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]-75.0472[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]-73.046388[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]40.8144[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]40.813078
[/TD]
[/TR]
[TR=“class: odd”]
[TD=“class: inline_edit not_null condition”]00602[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]-67.1849[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]-67.186553[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]18.3840[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]18.383005[/TD]
[/TR]
[TR=“class: even”]
[TD=“class: inline_edit not_null condition”]00602[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]-67.1849[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]-67.186553[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]18.3840[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]18.383005[/TD]
[/TR]
[TR=“class: odd”]
[TD=“class: inline_edit not_null condition”]00602[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]-67.1849[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]-67.186553[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]18.3840[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]18.383005[/TD]
[/TR]
[TR=“class: even”]
[TD=“class: inline_edit not_null condition”]00602[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]-67.1849[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]-67.186553[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]18.3840[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]18.383005[/TD]
[/TR]
[/TABLE]

As you may be able to tell, the one highlighted in red is the staged error… All the others (and I don’t know why it’s returning multiple rows of each) match and should not show up, based on my understanding of the query.

Can anyone point out my error and set me straight?

Thanks!

2

Further testing:

 SELECT t.zip, FORMAT(t.lon, 2) AS trunc_lon, FORMAT(e.lon, 2) AS ext_lon, FORMAT(t.lat, 2) AS trunc_lat, FORMAT(e.lat, 2) AS ext_lat
  FROM zip_trunc AS t, zip_ext AS e
  WHERE
  (
    FORMAT(t.lat, 2) != FORMAT(e.lat, 2)
    OR FORMAT(t.lon, 2) != FORMAT(e.lon, 2)
  )
  AND t.zip = e.zip

[TABLE=“class: data ajax”]
[TR]
[/TR]
[TR=“class: odd”]
[TD=“class: inline_edit not_null condition”]00501[/TD]
[TD=“class: inline_edit not_null”]-75.05[/TD]
[TD=“class: inline_edit not_null”]-73.05[/TD]
[TD=“class: inline_edit not_null”]40.81[/TD]
[TD=“class: inline_edit not_null”]40.81[/TD]
[/TR]
[TR=“class: even”]
[TD=“class: inline_edit not_null condition”]00501[/TD]
[TD=“class: inline_edit not_null”]-75.05[/TD]
[TD=“class: inline_edit not_null”]-73.05[/TD]
[TD=“class: inline_edit not_null”]40.81[/TD]
[TD=“class: inline_edit not_null”]40.81[/TD]
[/TR]
[TR=“class: odd”]
[TD=“class: inline_edit not_null condition”]00602[/TD]
[TD=“class: inline_edit not_null”]-67.18[/TD]
[TD=“class: inline_edit not_null”]-67.19[/TD]
[TD=“class: inline_edit not_null”]18.38[/TD]
[TD=“class: inline_edit not_null”]18.38[/TD]
[/TR]
[TR=“class: even”]
[TD=“class: inline_edit not_null condition”]00602[/TD]
[TD=“class: inline_edit not_null”]-67.18[/TD]
[TD=“class: inline_edit not_null”]-67.19[/TD]
[TD=“class: inline_edit not_null”]18.38[/TD]
[TD=“class: inline_edit not_null”]18.38[/TD]
[/TR]
[TR=“class: odd”]
[TD=“class: inline_edit not_null condition”]00602[/TD]
[TD=“class: inline_edit not_null”]-67.18[/TD]
[TD=“class: inline_edit not_null”]-67.19[/TD]
[TD=“class: inline_edit not_null”]18.38[/TD]
[TD=“class: inline_edit not_null”]18.38[/TD]
[/TR]
[TR=“class: even”]
[TD=“class: inline_edit not_null condition”]00602[/TD]
[TD=“class: inline_edit not_null”]-67.18[/TD]
[TD=“class: inline_edit not_null”]-67.19[/TD]
[TD=“class: inline_edit not_null”]18.38[/TD]
[TD=“class: inline_edit not_null”]18.38[/TD]
[/TR]
[TR=“class: odd”]
[TD=“class: inline_edit not_null condition”]00602[/TD]
[TD=“class: inline_edit not_null”]-67.18[/TD]
[TD=“class: inline_edit not_null”]-67.19[/TD]
[TD=“class: inline_edit not_null”]18.38[/TD]
[TD=“class: inline_edit not_null”]18.38[/TD]
[/TR]
[/TABLE]

Note that the 2nd and 3rd columns starting on the third line do not match because the FORMAT() function is rounding up the numbers.

Gotta figure out how to stop that…

3 

select t.zip, 
       t.lon as trunc_lon, 
       e.lon as ext_lon, 
       t.lat as trunc_lat, 
       e.lat as ext_lat
  from zip_trunc as t
  join zip_ext as e
    on t.zip = e.zip
 where cast(t.lat as dec(5,2)) <> cast(e.lat as dec(5,2))
    or cast(t.lon as dec(5,2)) <> cast(e.lon as dec(5,2))
4

Thanks, swampBoogie.

I wish that worked. I still get rounding… Here are the results.

select t.zip, 
       cast(t.lon as dec(5,2)) as trunc_lon, 
       cast(e.lon as dec(5,2)) as ext_lon, 
       t.lat as trunc_lat, 
       e.lat as ext_lat
  from zip_trunc as t
  join zip_ext as e
    on t.zip = e.zip
 where cast(t.lat as dec(5,2)) <> cast(e.lat as dec(5,2))
    or cast(t.lon as dec(5,2)) <> cast(e.lon as dec(5,2))

[TABLE=“class: data ajax”]
[TR=“class: odd”]
[TD=“class: inline_edit not_null”]00602[/TD]
[TD=“class: inline_edit not_null nowrap, align: right”]-67.18
[/TD]
[TD=“class: inline_edit not_null nowrap, align: right”]-67.19[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]18.3840[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]18.383005[/TD]
[/TR]
[TR=“class: even”]
[TD=“class: inline_edit not_null”]00602[/TD]
[TD=“class: inline_edit not_null nowrap, align: right”]-67.18[/TD]
[TD=“class: inline_edit not_null nowrap, align: right”]-67.19[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]18.3840[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]18.383005[/TD]
[/TR]
[TR=“class: odd”]
[TD=“class: inline_edit not_null”]00602[/TD]
[TD=“class: inline_edit not_null nowrap, align: right”]-67.18[/TD]
[TD=“class: inline_edit not_null nowrap, align: right”]-67.19[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]18.3840[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]18.383005
[/TD]
[/TR]
[TR=“class: even”]
[TD=“class: inline_edit not_null”]00602[/TD]
[TD=“class: inline_edit not_null nowrap, align: right”]-67.18[/TD]
[TD=“class: inline_edit not_null nowrap, align: right”]-67.19[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]18.3840[/TD]
[TD=“class: inline_edit not_null condition nowrap, align: right”]18.383005[/TD]
[/TR]
[/TABLE]

5

http://dev.mysql.com/doc/refman/5.5/en/mathematical-functions.html#function_truncate

6

Thanks you two!