www.webdeveloper.com
Results 1 to 4 of 4

Thread: Reffering to float column type in WHERE clause.

Hybrid View

  1. #1
    Join Date
    Aug 2009
    Location
    Ottawa, On
    Posts
    12

    Reffering to float column type in WHERE clause.

    Hey all,

    I have this query which at my knowledge should run pretty smoothly:

    Code:
    SELECT E.PhoneNumber
    FROM itrdemployees E 
    INNER JOIN itrdtrpt T 
    ON E.EmpNumber = T.EmpNumber
    INNER JOIN itrdinquirysections S
    ON T.TransferPoint = S.EngTransPoint 
    WHERE S.Section = 2.1 AND T.ActiveContact = 1
    S.Section is a float type column and when I run the query it doesn't show me any results when I know that 2.1 should return a phone number. In fact it doesn't run with any float values. S.Section contains both integers and float type values (1 ; 1.1 ; 1.2; 2; 2.1; 2.2 etc...) Basically it stores sections along with sub section. Thing is, it runs smoothly when I put in 2 instead of 2.1 or any integer values for that matter.

    doesn't work:
    Code:
    WHERE S.Section = 2.1 AND T.ActiveContact = 1
    works:
    Code:
    WHERE S.Section = 2 AND T.ActiveContact = 1
    Is there some sort of cast I must do around the value before running the query? Say: ...WHERE (float)2.1 AND...

    ps: I know that it should return data with the float values, I have checked with the current data in the database.

    Any pointers would be greatly appreciated. Thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    Refer to http://dev.mysql.com/doc/refman/5.0/...ith-float.html

    FLOATs should not be relied on for lookups of this sort. If you need to perform numerical calculations on these fields, use DECIMALs. Otherwise, you may use VARCHARs.

    Another option is actually using a series of INTs ... but you'll want to avoid that if you don't have any special computational needs.

    That help?

  3. #3
    Join Date
    Aug 2009
    Location
    Ottawa, On
    Posts
    12
    Thanks for the reply, I'll have to check it out next week to see if it works.

  4. #4
    Join Date
    Aug 2009
    Location
    Ottawa, On
    Posts
    12

    Smile

    I just wanted to get back at this to say that converting to a decimal column worked just fine. Thank you svidgen.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles