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.
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 1Error 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.JimUsers <> 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_idFROM (((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_idThe 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_idFROM 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_IDGROUP BY pro.emp_id, emp.emp_name, s_time.s_time_idThe 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 viewBrett8-) |
|
|
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_IDINNER JOIN job ON pro.pro_ID=job.pro_IDINNER 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?Brett8-) |
|
|
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_IDINNER JOIN job ON pro.pro_ID=job.pro_IDINNER JOIN s_time ON job.job_ID=s_time.job_ID GROUP BY pro.emp_id, emp.emp_name, s_time.s_time_idThe 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_idwhat 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, useCOUNT(*)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 * FROMYourTableWHERE 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_idI 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 |
|
|
|
|
|
|
|