June 24, 2010 at 5:14 pm
We have a database in a MS SQL 2005 SP1 server.
We have a select statement that joins two tables and converts a field from table 2 from a varchar to a decimal. The field that is a varchar has only numbers in it and we have verified that using isnumeric(). Our select statement has worked for years and suddedly we began getting an "error converting varchar to numeric" error. We found that a few strange things:
1. Running it on old data that it ran successful on now produces the error
2. Selecting a range of records it is successful one minute and not the next
3. If we include the filed being converted in the select as a seperate column with no conversion along with the column where we are converting it the error goes away:
This works
Select varcharfield, cast(isnull(rtrim(varcharfield), 0) as Decimal (10,)) from table
This does not
Select cast(isnull(rtrim(varcharfield), 0) as Decimal (10,)) from table
Also there is a second field that is unrelated that if we remove its converstion from varchar to nchar the error goes away.
We have run reindexing, checkdb, integrity checks, etc... and no errors.
Anyone run into this?
Okay. We inserted the the records into a new table as well was restored a backup from three months ago and ran it on the same tables. Still having the issue. Here is the syntax trimmed down:
SELECT
CONVERT(DECIMAL(10,0), RTRIM(ISNULL(M.BANKNAME, '0'))) AS TransitNumber,
CAST(RTRIM(M.CRCRDNUM) as NCHAR(17)) AS AccountNumber,
CAST(RIGHT(LEFT('0000000000' + CAST(T.ORTRXAMT *100 as varchar(50)), LEN('0000000000' + CAST(T.ORTRXAMT *100 as varchar(50)))-6), 10) as DECIMAL(20)) AS TRXAMOUNT,
CAST(T.CUSTNMBR AS NCHAR(15)) AS CUSTNMBR,
CAST(M.CUSTNAME AS NCHAR(22)) AS CUSTNAME
FROM
METST..RM20101 T
INNER JOIN
METST..RM00101 M
ON T.CUSTNMBR = M.CUSTNMBR
WHERE
RTRIM(T.BACHNUMB) = '032810ROYAL' and
T.RMDTYPAL = 9
ORDER BY
T.DOCNUMBR
When I run this I get the error "Msg 8114, Level 16, State 5, Line 3 Error converting data type varchar to numeric.". If comment out certain lines it works while others I get the error. Here is what I tried:
1. Comment out line Column 1 and it works
2. Comment out only Column 2 and I get the error
3. Comment out ony Column 3 and it works
4. Comment out only Column 4 and it works
5. Comment out only Column 5 and I get the error
Also, we can get the whole select to work if we modifiy Column 1 to covert to an INT first like this:
SELECT
CAST(CAST(RTRIM(ISNULL(M.BANKNAME, 0)) AS INT) AS DECIMAL(10,0)) AS TransitNumber,
CAST(RTRIM(M.CRCRDNUM) as NCHAR(17)) AS AccountNumber,
CAST(RIGHT(LEFT('0000000000' + CAST(T.ORTRXAMT *100 as varchar(50)), LEN('0000000000' + CAST(T.ORTRXAMT *100 as varchar(50)))-6), 10) as DECIMAL(20)) AS TRXAMOUNT,
CAST(T.CUSTNMBR AS NCHAR(15)) AS CUSTNMBR,
CAST(M.CUSTNAME AS NCHAR(22)) AS CUSTNAME
FROM
METST..RM20101 T
INNER JOIN
METST..RM00101 M
ON T.CUSTNMBR = M.CUSTNMBR
WHERE
RTRIM(T.BACHNUMB) = '032810ROYAL' and
T.RMDTYPAL = 9
ORDER BY
T.DOCNUMBR
June 25, 2010 at 1:55 am
It's a common misinterpretation to assume ISNUMERIC() will work like "OnlyNumbers".
Here's an alternative:
DECLARE @a VARCHAR(10)
SET @a='1e10'
SELECT ISNUMERIC(@a)
IF @a NOT LIKE '%[^0-9]%'
SELECT 'numeric'
ELSE SELECT 'not numeric'
You could also search this site for ISNUMERIC() and you'll find numerous articles dealing with the issue as well as alternative ways how to check for "Digits only".
Example:
http://www.sqlservercentral.com/Forums/Topic816101-338-1.aspx
June 25, 2010 at 3:21 am
IsNumeric doesn't guarantee that your string can be converted into numeric datatype (as was mentioned in the previous post). Actually, it may show return false, when value can be converted. For example, IsNumeric('') will return 0, but CAST('' as INT) will work!
Your problem can be nothing to do with IsNumeric function at all (if of cause, all the values you are trying to insert are convertable into numeric).
Are you aware that in SQL2005 the following query, in some circumstances, may return dvision by zero error:
SELECT Col1/Col2 FROM MyTable WHERE Col2 != 0
Yes, SQL Server optimiser may deside to evaluate calc. result before applying any filter! Usually it happens when you have quite large dataset and few joins in the query. I think you have the simmilar issue with your conversion (which is also possible and I have seen it as well many times). In the project I was involved, we have started to use SQL2005 even before its beta release. We operated with very large datasets and when we've found this behaviour, MS confirmed that is now the feature of SQL Server optimiser. There few different approaches to this issue:
For example to avoid devision by zero, you can use the following:
SELECT Col1/NULLIF(Col2,0) FROM MyTable WHERE Col2 != 0
In your case, you better check if the value convertable into numeric and if not replace it with null, something like:
SELECT CASE WHEN IsNumeric(Col1) = 1 THEN CAST(Cal1 as Decimal) ELSE NULL END, ...
FROM MyTable
WHERE IsNumeric(Col1) = 1
Again, remember: using IsNumeric doesn't always help. You might need to use a better check for this (again, you can find it in the prev. post)
June 25, 2010 at 4:31 am
What exactly is meant to be output to column 3? What datatype is T.ORTRXAMT?
DECLARE @ORTRXAMT DECIMAL(10,2)
SET @ORTRXAMT = 999.99
SELECT TRXAMOUNT =
CAST(
RIGHT(
LEFT('0000000000' + CAST(@ORTRXAMT *100 as varchar(50)), LEN('0000000000' + CAST(@ORTRXAMT *100 as varchar(50)))-6)
, 10)
as DECIMAL(20))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 25, 2010 at 7:58 am
I ran that exact script to test for numerics and a few others and they all came back as numerics. In regards to T.ORTRXAMT, even if I don't have that in this Select I still have the problem. Also, I can make this script work fine if I comment out CAST(T.CUSTNMBR AS NCHAR(15)) AS CUSTNMBR, which is crazy since that isn't even related and isn't coverting to Decimal. If I take out "CAST(T.CUSTNMBR AS NCHAR(15)) AS CUSTNMBR" it works if I leave it in I get the "varchar can't be converted to decimal" error.
June 25, 2010 at 8:29 am
Beratung (6/25/2010)
I ran that exact script to test for numerics and a few others and they all came back as numerics. In regards to T.ORTRXAMT, even if I don't have that in this Select I still have the problem. Also, I can make this script work fine if I comment out CAST(T.CUSTNMBR AS NCHAR(15)) AS CUSTNMBR, which is crazy since that isn't even related and isn't coverting to Decimal. If I take out "CAST(T.CUSTNMBR AS NCHAR(15)) AS CUSTNMBR" it works if I leave it in I get the "varchar can't be converted to decimal" error.
Try commenting out the other way round, i.e. selecting only one column. Regardless of the result of this excercise, I reckon you're having this problem because you're changing the plan by commenting out different columns, and plan changes are causing different sets of rows to be included in intermediate result sets.
Lutz' definitive "IsNumeric" code should be applied to each of the columns which are transformed from varchar to a numeric data type - the whole table, not just a subset.
Check the code I pointed out to you earlier - it's probably unrelated - it returns some unexpected results.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply