SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Comparing truncated numbers query returning some odd results.

    Comparing truncated numbers query returning some odd results. I'm sure it's my query (go figure )...

    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.

    Code:
     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:

    00501
    -75.0472 -73.046388 40.8144 40.813078
    00501
    -75.0472 -73.046388 40.8144 40.813078
    00602 -67.1849 -67.186553 18.3840 18.383005
    00602 -67.1849 -67.186553 18.3840 18.383005
    00602 -67.1849 -67.186553 18.3840 18.383005
    00602 -67.1849 -67.186553 18.3840 18.383005


    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!
    John

  2. #2
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Further testing:

    Code:
     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
    00501 -75.05 -73.05 40.81 40.81
    00501 -75.05 -73.05 40.81 40.81
    00602 -67.18 -67.19 18.38 18.38
    00602 -67.18 -67.19 18.38 18.38
    00602 -67.18 -67.19 18.38 18.38
    00602 -67.18 -67.19 18.38 18.38
    00602 -67.18 -67.19 18.38 18.38

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

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    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. #4
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, swampBoogie.

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

    Code:
    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))
    00602 -67.18
    -67.19 18.3840 18.383005
    00602 -67.18 -67.19 18.3840 18.383005
    00602 -67.18 -67.19 18.3840 18.383005
    00602 -67.18 -67.19 18.3840 18.383005
    John

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,491
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)

  6. #6
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks you two!
    John


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •