| Author |
Topic |
|
raguyazhin
Posting Yak Master
105 Posts |
 Posted - 2013-02-13 : 04:49:02
|
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
52326 Posts |
Posted - 2013-02-13 : 05:13:28
|
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/
|
 |
|
|
raguyazhin
Posting Yak Master
105 Posts |
Posted - 2013-02-13 : 06:41:36
|
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
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-13 : 06:43:32
|
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/
|
|
|
|
raguyazhin
Posting Yak Master
105 Posts |
Posted - 2013-02-13 : 06:57:12
|
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
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-13 : 07:02:37
|
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/
|
|
|
|
raguyazhin
Posting Yak Master
105 Posts |
Posted - 2013-02-13 : 07:09:42
|
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
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-13 : 07:12:58
|
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/
|
|
|
|
|
Convert Varchar to Decimal