CONVERTING DATA TYPE VARCHAR TO NUMERIC ERROR

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

CONVERTING DATA TYPE VARCHAR TO NUMERIC ERROR

Author

Topic

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-03-31 : 09:47:24

HELLO FRIENDS,
When i RUN A QUERY , I got this error. I looked at the data, there is nothing wrong. Do you know why this error occur and how to fix it?

Warning: Null value is eliminated by an aggregate or other SET operation.
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Thanks,
Jenny.

The stupid question is the question you don't ask.
www.single123.com

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-03-31 : 09:50:39

Please Post the Querry jenny.

Jim
Users <> Logic

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-03-31 : 10:20:03

Here is my query:

SELECT con.con_id, con.con_name,
fin_s_1.Sum_c_Hours, fin_s_1.Sum_c_Labor, fin_s_1.Sum_c_Travel, fin_s_1.Sum_c_ODC,
COALESCE(fin_s_1.Sum_c_Labor,0)+COALESCE(fin_s_1.Sum_c_Travel,0)+COALESCE(fin_s_1.Sum_c_ODC,0) AS TOTAL_c,
con_job.countofjob_hours_id
FROM (((con LEFT JOIN con_incurred ON con.con_id = con_incurred.con_id) LEFT JOIN con_invoice ON con.con_id =
con_invoice.con_id) LEFT JOIN con_job ON con.con_id =
con_job.con_id) INNER JOIN fin_s_1 ON con.con_id = fin_s_1.con_id

The stupid question is the question you don't ask.
www.single123.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-31 : 10:29:11

What datatype are these various Sum_c_XXX columns?

- Jeff

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-03-31 : 10:35:30

They are numeric.
fin_s_1 sql view works just fine.
Jenny.

The stupid question is the question you don't ask.
www.single123.com

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-03-31 : 10:48:29

Hello, I go back to check the one before this, and this view generates error. Can you please help me to fix it?
Thanks.

SELECT pro.emp_id, emp.emp_name, s_time.s_time_id,
Sum(IsNull(s_time.s_time,0)) AS sumofs_time,
Sum(IsNull(s_time.l_sal,0)) AS sumofl_sal,
Sum(IsNull(s_time.t_sal,0)) AS sumoft_sal,
Sum(IsNull(s_time.o_sal,0)) AS sumofo_sal,
Count(IsNull(s_time.s_time_id,0)) AS countofs_time_id
FROM emp INNER JOIN(pro INNER JOIN (job INNER JOIN s_time ON job.job_ID=s_time.job_ID) ON pro.pro_ID=job.pro_ID) ON emp.emp_ID=pro.emp_ID
GROUP BY pro.emp_id, emp.emp_name, s_time.s_time_id

The stupid question is the question you don't ask.
www.single123.com

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-31 : 10:51:41

I would say it's a mismatch in the joins then.

And why are you ouuter joining to these table I commented out.

They're not needed.


    SELECT c.con_id
	 , c.con_name
	 , f.Sum_c_Hours
	 , f.Sum_c_Labor
	 , f.Sum_c_Travel
	 , f.Sum_c_ODC
	 , COALESCE(f.Sum_c_Labor,0)+COALESCE(f.Sum_c_Travel,0)+COALESCE(f.Sum_c_ODC,0) AS TOTAL_c
	 , cj.countofjob_hours_id
      FROM (((con c
/*
 LEFT JOIN con_incurred ci 
        ON c.con_id = ci.con_id) 
 LEFT JOIN con_invoice cv 
        ON c.con_id = cv.con_id) 
*/
 LEFT JOIN con_job cj 
        ON c.con_id = cj.con_id) 
INNER JOIN fin_s_1 f
	ON c.con_id = f.con_id

Post the DDL of Tables and sp_help on the view

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-31 : 10:59:42

And what's the difference between the view you posted and this?


    SELECT  pro.emp_id
	 ,  emp.emp_name
	 ,  s_time.s_time_id
	 ,  Sum(IsNull(s_time.s_time,0)) AS sumofs_time 
	 ,  Sum(IsNull(s_time.l_sal,0))  AS sumofl_sal 
	 ,  Sum(IsNull(s_time.t_sal,0))  AS sumoft_sal 
	 ,  Sum(IsNull(s_time.o_sal,0))  AS sumofo_sal 
	 ,  Count(IsNull(s_time.s_time_id,0)) AS countofs_time_id
      FROM  emp 
INNER JOIN  pro 
        ON  emp.emp_ID=pro.emp_ID
INNER JOIN  job 
        ON  pro.pro_ID=job.pro_ID
INNER JOIN  s_time 
        ON  job.job_ID=s_time.job_ID 
  GROUP BY pro.emp_id, emp.emp_name, s_time.s_time_id

Is it the same error?

We'd need to see the DDL.

Did you double click on the error message?

Brett

8-)

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-03-31 : 11:16:35

It is the same error.
It worked yesterday. Today, I used EM to modify the design yesterday by changing from numeric to varchar for one table, (RIGHT-CLICK ON TABLE NAME, GO TO DESIGN, then I changed datatype)but I changed it back to the way it was. Then, it generate an error.

SELECT pro.emp_id
, emp.emp_name
, s_time.s_time_id
, Sum(IsNull(s_time.s_time,0)) AS sumofs_time
, Sum(IsNull(s_time.l_sal,0)) AS sumofl_sal
, Sum(IsNull(s_time.t_sal,0)) AS sumoft_sal
, Sum(IsNull(s_time.o_sal,0)) AS sumofo_sal
, Count(IsNull(s_time.s_time_id,0)) AS countofs_time_id
FROM emp
INNER JOIN pro
ON emp.emp_ID=pro.emp_ID
INNER JOIN job
ON pro.pro_ID=job.pro_ID
INNER JOIN s_time
ON job.job_ID=s_time.job_ID
GROUP BY pro.emp_id, emp.emp_name, s_time.s_time_id

The stupid question is the question you don't ask.
www.single123.com

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-03-31 : 11:46:04

I doublechecked all related tables' datatype, they are all NUMERIC.
Why getting the error?
thanks,
Jenny.

The stupid question is the question you don't ask.
www.single123.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-31 : 12:43:13

>>Count(IsNull(s_time.s_time_id,0)) AS countofs_time_id

what are you trying to return here? just the row count, or the # of s_time_Id's that are not null?

for the row count, use

COUNT(*)

for the # of s_time_Id's that are not null, just use:

COUNT(s_time_id)

And, again, you have no choice but to go through your table to ensure that all columns have the proper datatypes and if not, that you examine the data to be sure that there are no non-numeric values in the columns you wish to convert to numbers. Finally, when your data is finally clean and you identify the problem, FIX THE TABLES and ensure all columns are set to the proper datatype. Do NOT just use VARCHAR everywhere, you have already seen firsthand all the trouble this causes.

try runnign queries like this to check for letters or non-numeric characters in your data:

SELECT * FROM
YourTable
WHERE ColumnToCheck like '%[a-z]%'

and stuff like that ... obviously only check Varchar columns that you should have set up to be numeric in the first place.

- Jeff

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-03-31 : 13:09:07

Count(IsNull(s_time.s_time_id,0)) AS countofs_time_id
I AM trying to return the numbers of s_time GROUP BY s_time_id. In Access, it worked just fine.
Each of my table has about 2000 rows.
Thanks,
Jenny.

The stupid question is the question you don't ask.
www.single123.com

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-01 : 10:43:56

Thanks for your help.
I decided to start over again. I mean, recreate the db, tables, views, dts..... I think it takes a few days.
Thanks,
Jenny.

The stupid question is the question you don't ask.
www.single123.com

vaidyanathans5390
Starting Member

1 Post

Posted - 2013-09-02 : 02:35:06

please answer to error
'Error converting data type varchar to numeric'
how to correct it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources