SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Convert Varchar to Decimal
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raguyazhin
Posting Yak Master

India
105 Posts

Posted - 02/13/2013 :  04:49:02  Show Profile  Reply with Quote
Hi,

Select LHP.LHP_PARAM_RESULT_VALUE,
Cast(Replace(LHP.LHP_PARAM_RESULT_VALUE,',','') as decimal(18,2))
from LS_HMT_PARAM_RESULTS LHP
Where
ISNUMERIC(LHP_PARAM_RESULT_VALUE) = 1 AND
LHP_PARAM_RESULT_VALUE <> ''

This Query Run some results and with Arithmetic overflow error converting varchar to data type numeric. Error

Each time the query running with various Record counts and with the Arithmetic overflow error.

Kindly Suggest Me

-- Ragu Thangavel

--
Ragu

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/13/2013 :  05:13:28  Show Profile  Reply with Quote
check the values in LHP.LHP_PARAM_RESULT_VALUE and see if they can be included within precision value of 18 and scale 2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

raguyazhin
Posting Yak Master

India
105 Posts

Posted - 02/13/2013 :  06:41:36  Show Profile  Reply with Quote
I expect 6400 records without error. But here i ran the query 200 Records came with Arithmetic overflow error converting varchar to data type numeric Error After i ran the same query 600 records are came with error again i ran the query 0 records with the error.



so what is the problem with the query are SQL Server

Sql Server Version is SQL Server 2005 EE.

--
Ragu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/13/2013 :  06:43:32  Show Profile  Reply with Quote
what does this return?

Select LHP.LHP_PARAM_RESULT_VALUE
from LS_HMT_PARAM_RESULTS LHP
Where
ISNUMERIC(LHP_PARAM_RESULT_VALUE) = 1 AND 
LEN(LHP_PARAM_RESULT_VALUE) > 19


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

raguyazhin
Posting Yak Master

India
105 Posts

Posted - 02/13/2013 :  06:57:12  Show Profile  Reply with Quote
This Query Returns 0 Records Because No records greater than 19
When we use cast that time the record counts are varying each time running the query..

quote:
Originally posted by visakh16

what does this return?

Select LHP.LHP_PARAM_RESULT_VALUE
from LS_HMT_PARAM_RESULTS LHP
Where
ISNUMERIC(LHP_PARAM_RESULT_VALUE) = 1 AND 
LEN(LHP_PARAM_RESULT_VALUE) > 19


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





--
Ragu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/13/2013 :  07:02:37  Show Profile  Reply with Quote
what does this return?

Select LHP.LHP_PARAM_RESULT_VALUE
from LS_HMT_PARAM_RESULTS LHP
Where
ISNUMERIC(LHP_PARAM_RESULT_VALUE) = 1 AND 
LHP_PARAM_RESULT_VALUE LIKE '%[^0-9,.]%'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

raguyazhin
Posting Yak Master

India
105 Posts

Posted - 02/13/2013 :  07:09:42  Show Profile  Reply with Quote
Data's are not a problem because this query ran when i transferred the this single column data's to another table.
but error came on the original table only.

both column data types are same

-- This Query Runs Successfully this Table has only one column.

Select LHP.LHP_PARAM_RESULT_VALUE,
Cast(Replace(LHP.LHP_PARAM_RESULT_VALUE,',','') as decimal(18,2))
from LS_HMT_PARAM_RESULTS_TEST LHP
Where
ISNUMERIC(LHP_PARAM_RESULT_VALUE) = 1 AND
LHP_PARAM_RESULT_VALUE <> ''

-- This Query has error with various record counts at various running

Select LHP.LHP_PARAM_RESULT_VALUE,
Cast(Replace(LHP.LHP_PARAM_RESULT_VALUE,',','') as decimal(18,2))
from LS_HMT_PARAM_RESULTS LHP
Where
ISNUMERIC(LHP_PARAM_RESULT_VALUE) = 1 AND
LHP_PARAM_RESULT_VALUE <> ''

--
Ragu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/13/2013 :  07:12:58  Show Profile  Reply with Quote
I dont understand what you're talking about. Do you mean it ran successfully with the CAST? didnt understand what you mean by

This Query Runs Successfully this Table has only one column.


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000