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.

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.

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

swampBoogie
—
2011-11-10T19:58:21Z —
#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))

PHP_John
—
2011-11-10T20:53:53Z —
#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))