Reffering to float column type in WHERE clause.

[GreenMushroom16]

Hey all,

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

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:

WHERE S.Section = 2.1 AND T.ActiveContact = 1

works:

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.

[svidgen]

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?

[GreenMushroom16]

Thanks for the reply, I'll have to check it out next week to see if it works.

[GreenMushroom16]

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