Click here to Skip to main content
15,867,771 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all,

While executing a query I ma getting an error as
Error converting data type varchar to numeric
.I have found out that the problem is with decimal data type.I have tried various ways but nothing helped.Any help will be really appreciated .

What I have tried:

SELECT ISNULL(cast((SUM(b+c+d+e+f+g+h+i+j+k+l+m+n+o+p+q))*1.5625 AS DECIMAL(18, 2)),0.00) as Pee from 
 (select (case when IN017_01 IS NOT NULL AND IN017_01 != '' then 1  else 0 end )as b , 
  (case when IN016_01 IS NOT NULL AND IN016_01 != '' then 1  else 0 end )as c , 
  (case when IN022_01 IS NOT NULL AND IN022_01 != '' then 1  else 0 end )as d , 
  (case when in068_04 IS NOT NULL AND in068_04 != '' then 1  else 0 end )as e , 
  (case when in068_05 IS NOT NULL AND in068_05 != '' then 1  else 0 end )as f , 
   (case when CAST(in068_06 AS DECIMAL(18,2)) IS NOT NULL AND CAST(in068_06 AS DECIMAL(18,2)) != '' then 1  else 0 end)as g , 
    (case when in068_07 IS NOT NULL AND in068_07 != '' then 1  else 0 end )as h ,
	(case when in068_08 IS NOT NULL AND in068_08 != '' then 1  else 0 end )as i , 
	(case when CAST(in068_09 AS DECIMAL(18,2)) IS NOT NULL AND CAST(in068_09 AS DECIMAL(18,2)) != '' then 1  else 0 end)as j ,
	 (case when in068_10 IS NOT NULL AND in068_10 != '' then 1  else 0 end )as k,  
	 (case when CAST(in068_11 AS DECIMAL(18,2)) IS NOT NULL AND CAST(in068_11 AS DECIMAL(18,2)) != '' then 1  else 0 end)as l ,  
	 (case when in068_12 IS NOT NULL AND in068_12 != '' then 1  else 0 end )as m ,
	 (case when CAST(in068_13 AS DECIMAL(18,2)) IS NOT NULL AND CAST(in068_13 AS DECIMAL(18,2)) != '' then 1  else 0 end)as n, 
	 (case when CAST(in068_15 AS DECIMAL(18,2)) IS NOT NULL AND CAST(in068_15 AS DECIMAL(18,2)) != '' then 1  else 0 end)as o , 
	 (case when CAST(in068_16 AS DECIMAL(18,2)) IS NOT NULL AND CAST(in068_16 AS DECIMAL(18,2)) != '' then 1  else 0 end)as p ,  
	 (case when IN027_02 IS NOT NULL AND IN027_02 != '' then 1  else 0 end )as q from IN068 WHERE IN068.IN027_02='60006' ) as t
Posted
Updated 5-Jun-18 8:00am
Comments
RedDk 5-Jun-18 14:26pm    
Questions like this really interest me. I like seeing code that appears to address some abstraction that is not a bare-bone leveraging of TSQL (particularly TSQL because it does things like BASIC does) tricks ... this stuff'll fly ...

BUT ... CAST and CONVERT is 'yer demon. Understand them and you'll understand why I'm not going to wade through this code without a table of data using my debugger.

All that said, I think you have a misunderstanding of the CAST and CONVERT methods.

1 solution

We can't help.
You need to look at your data carefully, and identify which row and column is giving you difficulties - and we can't do that for you as we have no access at all to your data.

But ... the number of CASTs there concerns me: why are you needing to cast so many columns? If you are storing numeric data in VARCHAR or NVARCHAR columns, then that's you problem - and the only way to prevent it now and in the future is to redesign your DB so all data is stored in appropriate types.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900