Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query throwing error when converting to Numeric Expand / Collapse
Author
Message
Posted Tuesday, July 31, 2012 12:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:37 AM
Points: 243, Visits: 107
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
Post #1337686
Posted Tuesday, July 31, 2012 1:27 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 4,976, Visits: 11,666
Can you provide us with DDL & sample data that will allow us to replicate this?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1337708
Posted Tuesday, July 31, 2012 3:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:37 AM
Points: 243, Visits: 107
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
Post #1337746
Posted Tuesday, July 31, 2012 5:29 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
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?
Post #1337801
Posted Tuesday, July 31, 2012 5:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:37 AM
Points: 243, Visits: 107
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
Post #1337805
Posted Tuesday, July 31, 2012 5:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
ISNUMERIC isn't very useful, see this article.

(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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1337814
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse