quote:

---

Originally posted by dcarva

Hello,

I have the following query that runs against a view:

select cast((convert(varchar(length), HD.Value)) as float)
from HardwareData HD
where (HD.PropertyName = 'Total Space' and HD.CategoryName = 'Disk Drive(s)')

This query works with no errors. When I add a where clause as follows, it breaks:

select cast((convert(varchar(length), HD.Value)) as float)
from HardwareData HD
where (HD.PropertyName = 'Total Space' and HD.CategoryName = 'Disk Drive(s)')
and cast((convert(varchar(length),HD.Value)) as float) > 9999999

I get a "Msg 8114, Error converting data type varchar to float." message. Why would the statement in the WHERE clause fail to convert to float and not the statement that's in the select statement? It's as if the where clause conversion is done against all of the data, not just against the subset specified by the first part of the where clause where I try to narrow it down by total space. Any ideas?

THANKS

---

quote:

---

Originally posted by dcarva

Thank you. However I still get the error with length specified.

---

quote:

---

Originally posted by dcarva

Let me simply the query a bit:

This works:

select cast(HD.Value as float)
from HardwareData HD
where (HD.PropertyName = 'Total Space' and HD.CategoryName = 'Disk Drive(s)')

This does not:

select cast(HD.Value as float)
from HardwareData HD
where (HD.PropertyName = 'Total Space' and HD.CategoryName = 'Disk Drive(s)')
and cast(HD.Value as float) > 9999999

The HD.Value column contains alphanumeric data. However, the first query above only brings back numeric data because of the where clause narrowing it down to 'Total Space' type. Therefore, I don't understand why the second query fails because it should be narrowing it down to only bringing back numeric data. If it was returning alphanumeric data, then the first query would fail.

Thanks

---

select cast(HD.Value as float)
from HardwareData HD
where (HD.PropertyName = 'Total Space' and HD.CategoryName = 'Disk Drive(s)')
and (isnumeric(HD.Value)=1 and  cast(HD.Value as float) > 9999999)

SELECT *
FROM
(
	select cast(HD.Value as float) AS Vaa
	from HardwareData HD
	where (HD.PropertyName = 'Total Space' and HD.CategoryName = 'Disk Drive(s)')
)
WHERE
	Val > 9999999