TYPE CAST ERROR

Question

TYPE CAST ERROR

Prashanth-372517

Valued Member

Points: 67
More actions
October 4, 2006 at 12:32 pm

#170537

In my sql query, in which I am reading through 9000 records and pulling out SUM (tableX.Quantity) , they seem to parse through fine until a certian point where it breaks saying that type varchar cannot be converted to type real.
Lets say you have something like this
DECLARE @TEMP AS REAL
SET @TEMP = (Select Sum(tableX.Quantity) ..... .... .... )
the SUM(tableX.Quantity) is coming out as real variables for all, but probably breaks when it finds bad data ...
either that or it is coming across negative values.
Can someone suggest a solution for this ?

Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

What is the datatype of tableX.Quantity? If it is a numeric datatype, a negative number will not cause a cast error. Bad data, in this case, may be bad design. Give us the datatypes of the columns involved and post your code and DDL if possible.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Roi Assa SSChasing Mays Points: 611 More actions · Answer 2

Try this one:

SELECT *

FROM tableX

WHERE ISNUMERIC(Quantity) = 0

Kindest Regards,

Roi Assa

noeld SSC Guru Points: 96590 More actions · Answer 3

Just for the record ISNUMERIC is not reliable either

* Noel

Loner SSC-Insane Points: 21279 More actions · Answer 4

Do you want @Temp to capture the sum of all the quantity? Then your query had a problem.

DECLARE @TEMP AS REAL

SELECT @Temp = SUM(CASE WHEN ISNUMERIC(tableX.Quantity) = 0

THEN 0

ELSE tableX.Quantity

END )

FROM Table

Prashanth-372517 Valued Member Points: 67 More actions · Answer 5

Prashanth-372517

Valued Member

Points: 67

October 5, 2006 at 3:13 pm

#664462

Thanks folks..it sure helped !!!!!

Jeff Moden SSC Guru Points: 1003946 More actions · Answer 6

Oh... no, no, no, no... ISNUMERIC does not adequately test for IS A NUMBER or IS ALL DIGITS...

Try this...

SELECT ISNUMERIC('$1,000')

SELECT CAST('$1,000' AS MONEY)

SELECT CAST('$1,000' AS INT)

GO

SELECT ISNUMERIC('0d05')

SELECT CAST('0d05' AS INT)

GO

Notice that both uses of ISNUMERIC return a 1 which most people take to mean as "IS ALL DIGITS" or even ISNUMERIC... you've got to be really careful with ISNUMERIC... for example, notice that the conversion to MONEY above works but the conversion to INT with the same value does not work?

And, there are more than two dozen characters and patterns of characters that ISNUMERIC will return a "1" for that are not numeric digits.

If you want to test for "IS ALL DIGITS", you must use something like the following...

SELECT collist
FROM sometable
WHERE somecharcol NOT LIKE '%[^0-9]%'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)