| Author |
Topic |
|
leahsmart
Posting Yak Master
133 Posts |
 Posted - 2004-07-19 : 06:20:47
|
Hi Reader!I have a query and in the where clause I have the followingwhere MyNumber <> '' I have this code on a number because I have a generic page the returns selections for numbers and strings.What I have found is that when I use the above it does not return any of the rows with zeros in. Why is this. Does 0 = ''. I think NOT!HelpThanksLeah |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-07-19 : 06:33:04
|
| show us some sample data that is being rejected....including the ddl of the table involved. |
 |
|
|
leahsmart
Posting Yak Master
133 Posts |
Posted - 2004-07-19 : 06:36:01
|
| The field type is float.The data it is rejecting is 0.0 |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-07-19 : 06:38:40
|
| you are comparing a float to '', using where mynumber <> '' ?*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-07-19 : 06:40:18
|
| create table testfl(fl float)insert into testfl values (0.0)select * from testflselect * from testfl where fl <> ''I get the same - I'm actually surprised it allowed the compare..this works:select * from testfl where str(fl) <> ''*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-19 : 07:15:25
|
| "I'm actually surprised it allowed the compare"Why's that?Isn't SQL basically doing an implicit CAST and saying select * from testfl where fl <> CONVERT(float, '')andSELECT CONVERT(float, '')seems to give me: "0.0" :)Kristen |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-07-19 : 07:44:38
|
I'd try a more complex condition... comparing numbers to strings has issues ...Perhaps 0.0 does equal CAST('' AS Float) ?I don't understand:quote:
I have this code on a number because I have a generic page the returns selections for numbers and strings.
quote:
The field type is float.
If the field type is float, it can't be a string too.My stretch is the field type is VARCHAR and you're storing floating point numbers in the VARCHAR string? You then want all non-blank VARCHARs?Try:WHERE LEN(MyNumber) > 0 |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-07-19 : 08:22:56
|
Hi Kristen,Yes - it obviously did the implicit conversion - I had expected it to complain initially. I guess I'm not always sure when an implicit conversion will occur.Sam - I believe you and Kristen are right - the conversion that Kristen did show it. So, to solve his problem, he will likely need to use the STR(fl) as I showed it, unless I've mis-understood the problem.Yeesh Kristen : 816 post ! And when I hit 500 you had 600 or so ... gonna hit 1000 by the weekend?  *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
leahsmart
Posting Yak Master
133 Posts |
Posted - 2004-07-19 : 08:24:34
|
| I am a she!I shall have a go with the str or the length later today. Thanks for all your help. I shall let you know how I get on. |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-07-19 : 08:27:28
|
My humble apologies, Madame and Mi'lady, for my ignorant assumption  'Tis but human I fear...Hope that those solution help, CiaO*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-07-19 : 09:00:09
|
| Please, use explicit conversions whenever you can ! ! ! ! Explicit sounds like a dirty word sometimes, but it'll save you from shouting out expletives later when you can't figure out why your code doesn't quite work!- Jeff |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-19 : 09:13:10
|
| "I guess I'm not always sure when an implicit conversion will occur"For numeric types implicit conversions occur pretty much everywhere EXCEPT when MONEY types are involved. Dunno why they should be excluded from the fun ...... SELECT DATEADD(Day, 1, 38185.592071296298) Kristen |
|
|
|
|
0 = ''