raguyazhin (2/13/2013)
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 <> ''
Actually the problem absolutely is the data. Don't confuse that with datatype of the structure of the table. What that means is that in one environment your query works because the VALUES are able to be converted. In the other environment there is at least one value that can't be converted. This is why it is so important to use the proper datatypes.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/