Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    7

    Comparing decimals....

    Hi,

    I have a query ...

    select c.month
    from summary c, summary v
    where c.location = 'Carlisle' and v.location = 'Newcastle'
    and c.minTemp > v.minTemp and c.maxTemp > v.maxTemp ;

    Which finds the months where the max and min temp were higher than carlisle

    However when it is executed the returned results are incorrect. It works fine when the min and max temp are ints, however once i make them decimal(3,2) format the query messes up...

    Any ideas how I can sort this out ?

    Thanks Chris

  2. #2
    Join Date
    May 2004
    Posts
    5

    More Info

    When you say the query messes up, what exactly happens, are the results invalid or is an error thrown. What error or describe the results.

    It should also be noted than since you do not join summary c & summary v by month, this query will return any month in c that had a higher temperature than any month in v. Is this intended?

    For example given:
    location month minTemp maxTemp
    Carlisle July 50 80
    Newcastle July 60 90
    Newcastle December 20 30

    July will appear since Carlisle's July was warmer than Newcastle's December

  3. #3
    Join Date
    Mar 2004
    Posts
    7
    Thanks for the reply...

    By messing up I mean extra results are returned that are clearly wrong. However this only happens when the min / max temp fields are stored as decimal(3,2) format.

    Thanks for pointing out the other problem, I didn't see that. I think this is probably what is causing the extra months to be returned by the query, nothing to do with the format.

    How can I use JOIN to make sure the temps compared are from the same month ?

    Again, thanks for the reply

    Chris

  4. #4
    Join Date
    May 2004
    Posts
    5
    You have two methods for making sure the months line up, just do another equality test in the where clause (c.month = v.month):

    select c.month
    from summary c, summary v
    where c.month = v.month
    and c.location = 'Carlisle' and v.location = 'Newcastle'
    and c.minTemp > v.minTemp and c.maxTemp > v.maxTemp ;

    Or change to a join syntax:

    select c.month
    from summary c
    INNER JOIN
    summary v
    ON c.month = v.month
    and c.minTemp > v.minTemp
    and c.maxTemp > v.maxTemp
    where
    c.location = 'Carlisle' and v.location = 'Newcastle' ;

  5. #5
    Join Date
    Mar 2004
    Posts
    7
    Cheers mate,

    Got it all sorted now...

    Thanks for the help !

    Chris

Posting Permissions

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