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 2000 Forums
 SQL Server Development (2000)
 CONVERTING DATA TYPE VARCHAR TO NUMERIC ERROR
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jennypretty
Yak Posting Veteran

96 Posts

Posted - 03/31/2005 :  09:47:24  Show Profile  Visit jennypretty's Homepage  Reply with Quote
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

USA
1535 Posts

Posted - 03/31/2005 :  09:50:39  Show Profile  Visit JimL's Homepage  Reply with Quote
Please Post the Querry jenny.

Jim
Users <> Logic
Go to Top of Page

jennypretty
Yak Posting Veteran

96 Posts

Posted - 03/31/2005 :  10:20:03  Show Profile  Visit jennypretty's Homepage  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/31/2005 :  10:29:11  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
What datatype are these various Sum_c_XXX columns?


- Jeff
Go to Top of Page

jennypretty
Yak Posting Veteran

96 Posts

Posted - 03/31/2005 :  10:35:30  Show Profile  Visit jennypretty's Homepage  Reply with Quote
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
Go to Top of Page

jennypretty
Yak Posting Veteran

96 Posts

Posted - 03/31/2005 :  10:48:29  Show Profile  Visit jennypretty's Homepage  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/31/2005 :  10:51:41  Show Profile  Reply with Quote
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-)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/31/2005 :  10:59:42  Show Profile  Reply with Quote
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-)
Go to Top of Page

jennypretty
Yak Posting Veteran

96 Posts

Posted - 03/31/2005 :  11:16:35  Show Profile  Visit jennypretty's Homepage  Reply with Quote
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
Go to Top of Page

jennypretty
Yak Posting Veteran

96 Posts

Posted - 03/31/2005 :  11:46:04  Show Profile  Visit jennypretty's Homepage  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/31/2005 :  12:43:13  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
>>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

Edited by - jsmith8858 on 03/31/2005 12:46:32
Go to Top of Page

jennypretty
Yak Posting Veteran

96 Posts

Posted - 03/31/2005 :  13:09:07  Show Profile  Visit jennypretty's Homepage  Reply with Quote
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
Go to Top of Page

jennypretty
Yak Posting Veteran

96 Posts

Posted - 04/01/2005 :  10:43:56  Show Profile  Visit jennypretty's Homepage  Reply with Quote
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
Go to Top of Page

vaidyanathans5390
Starting Member

India
1 Posts

Posted - 09/02/2013 :  02:35:06  Show Profile  Reply with Quote
please answer to error
'Error converting data type varchar to numeric'
how to correct it
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.12 seconds. Powered By: Snitz Forums 2000