July 31, 2012 at 12:40 am
Hi Experts,
Please help me in this.
I have a table where i am storing numeric value in Varchar column.
when i am trying to convert it to numeric its throwing an error. Please find the error and query below.
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
SELECT * FROM MyDB..MyTable where BatchFileId=5 AND CAST(LineItemValue AS NUMERIC(28,5))=1873.76
if i convert it to float and run the below query, then its working fine.
SELECT * FROM MyDB..MyTable
where BatchFileId=5 AND CAST(LineItemValue AS FLOAT)=1873.76
Please help me to fix this problem.
Thanks in Advance.
Regards,
Vijay
July 31, 2012 at 1:27 am
Can you provide us with DDL & sample data that will allow us to replicate this?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 31, 2012 at 3:15 am
Hi,
Please find the code below.
CREATE TABLE MyTable
(
BatchFileID INT IDENTITY,
LineItemValue NVARCHAR(256)
)
INSERT MyTable
SELECT 'EFC'
UNION
SELECT '3456.5070'
UNION
SELECT '253434.5670'
UNION
SELECT '345.5070'
UNION
SELECT '1234.5670'
select * from MyTable
WHERE
--CAST(LineItemValue AS NUMERIC(28,5))=1234.5670 AND
LineItemValue NOT LIKE '%E%'
AND ISNUMERIC(LineItemValue)=1
select * from MyTable
WHERE
CAST(LineItemValue AS NUMERIC(28,5))=1234.5670 AND
LineItemValue NOT LIKE '%E%'
AND ISNUMERIC(LineItemValue)=1
DROP TABLE MyTable
I am unable to reproduce the scenario for float. once i reproduce i will post the code back.
But above code for converting nvarchar to numeric should work. Just wanted to know why it is not working.
Thanks,
Vijay
July 31, 2012 at 5:29 am
Hi Vijay
I've tried a few options on this one, and the only one that works is to create a temp table with the Numeric values (Excluding any alpha), then select from the temp table to get the required values.
The execution plan is always converting the query to a single table scan & outputting LineItemValue, so that's why it fails.
Why it's doing that & how to stop it, I don't know.
Examples: (which have the same problem)
SELECT BatchFileId, Num1
FROM
(
SELECT BatchFileId,
CAST(LineItemValue AS NUMERIC(28,5)) as Num1
FROM MyTable
WHERE LineItemValue NOT LIKE '%E%'
) X
WHERE Num1=1234.5670
SELECT T.BatchFileId,
X.Num1
FROM MyTable T
CROSS APPLY (SELECT CAST(T.LineItemValue AS NUMERIC(28,5)) as Num1
WHERE T.LineItemValue NOT LIKE '%E%') X
WHERE ISNUMERIC(X.Num1)=1 AND X.Num1=1234.5670
Maybe someone else knows what's happening & how to fix it?:ermm:
July 31, 2012 at 5:34 am
Thanks laurie:-).
Even i tried the first Query and its working fine. But just wanted to know why its is not working in Usual way.
Regards,
Vijay
July 31, 2012 at 5:52 am
ISNUMERIC isn't very useful, see this article[/url].
(CROSS/OUTER) APPLY can be a handy and efficient alternative:
SELECT
m.BatchFileId,
x.Num1
FROM MyTable m
CROSS APPLY (
SELECT Num1 = CAST(
CASE WHEN m.LineItemValue NOT LIKE '%E%' THEN m.LineItemValue ELSE NULL END
AS NUMERIC(28,5))
) x
WHERE x.Num1 IS NOT NULL
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