Ererror during comparison - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Ererror during comparison

Author

Topic

raviravi
Starting Member

8 Posts

Posted - 2006-10-13 : 03:02:33

Dear all,
Please help me……….to me this is very strange thing
My problem is I have say one table Result (mksobt varchar ,maxmks varchar) and my query is
Select * from result where (cast (mksobt as float)\cast (maxmks as float) ) * 100 >30
“The error is unable to convert varchar data type to float “
but this query run fine
Select *, (cast (mksobt as float)\cast (maxmks as float) ) * 100
Please suggest me what can be done?
Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-13 : 03:07:14

[code]select * from result
where isnumber(mksobt) = 1 and isnumber(maxmks) = 1
and 3 * convert(float, mksobt) > convert(float, maxmks)[/code]This way, you don't need to worry for division by zero.

Peter Larsson
Helsingborg, Sweden

raviravi
Starting Member

8 Posts

Posted - 2006-10-13 : 03:13:51

thanx peter for ur support
but as i mentioned above that the same thing runs fine is select clause
the error occurs onle while comparing it .............usin > or <

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-13 : 03:44:22

Do you have some sample data? Sample data for which this error occurs?

Peter Larsson
Helsingborg, Sweden

raviravi
Starting Member

8 Posts

Posted - 2006-10-13 : 04:48:29

Thanx Petere for ur efforts
my query is solved
i have added one more condition to my actual query and it is done.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-13 : 04:52:04

Care to share the solution to other people?
So they also can benefit from our efforts?

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-13 : 09:01:18

Why did you use Varchar datatype to store numbers?

Peso, ISNUMERIC is not always reliable
http://aspfaq.com/show.asp?id=2390

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-13 : 09:21:21

No, I know that Madhi (I have participated in several discussions about that),
but it is reliable enough to check if division is doable.

That's all I need to know in this query.

SELECT	*
FROM	(
		SELECT	mksobt,
			maxmks
		FROM	Result
		WHERE	ISNUMERIC(mksobt) = 1
			AND ISNUMERIC(maxmks) = 1
	) d
WHERE	3 * CONVERT(float, mksobt) > CONVERT(float, maxmks)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources