InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 19:35:21 GMT20http://www.sqlservercentral.com/Forums/Topic532612-8-1.aspxhi everyone.....i am the beginner of sql......can anyone help me to find out the mistake where i made????ive tried many times bt again nd again i gt same result"null value is eliminated by an aggregate or other set operation'.....my code completed successfully bt which doesnt show the result bcz of this warning message....can anyone help me??????declare getcur cursor for select sd.roll_no from student_details sd where sd.degree_id = @degree_id AND sd.branch_id = @branch_id open getcur FETCH NEXT FROM getcur INTO @roll_no WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #temp SELECT sd.reg_no, sd.student_name, bd.branch_name, cd.course_name, r.noof_semester, max(isnull(sm.sem_attended,0))as sem_attended, @is_completed as is_completed, rs.sub_code, rs.sub_name, case when sm.int_mark_obtained is null then 0 else @int_mark_obtained end, case when sm.ext_mark_obtained is null then 0 else @ext_mark_obtained end, isnull(sm.int_mark_obtained,0)+isnull(sm.ext_mark_obtained,0)as total_marks, case when sm.exam_status='p' then 'PASS' when sm.exam_status= 'f' then 'fail' end exam_status FROM student_details sd INNER JOIN student_marks sm ON sd.roll_no = sm.roll_no INNER JOIN regulation_subject rs ON rs.regulation_sub_id = sm.regulation_sub_id INNER JOIN regulation r ON r.regulation_no= rs.regulation_no INNER JOIN course_details cd ON cd.course_id = sm.course_id INNER JOIN branch_details bd ON bd.branch_id=sd.branch_id WHERE @noof_semester =(select max(@sem_attended) from student_marks sm where sm.roll_no=@roll_no) AND sd.roll_no = sm.roll_no and @batch=2007 and @course_id=99 group by sd.reg_no, sd.student_name, bd.branch_name, cd.course_name, r.noof_semester, rs.sub_code, rs.sub_name, sm.int_mark_obtained, sm.ext_mark_obtained, sm.exam_status if @noof_semester=max(@sem_attended) begin set @is_completed='yes' end else begin set @is_completed='no' end fetch next from getcur into @roll_no end select *from #temp order by reg_no close getcurdeallocate getcurdrop table #tempTue, 22 Jan 2013 04:29:54 GMTnitha jenhttp://www.sqlservercentral.com/Forums/Topic532612-8-1.aspxhi everyone.....i am the beginner of sql......can anyone help me to find out the mistake where i made????ive tried many times bt again nd again i gt same result"null value is eliminated by an aggregate or other set operation'.....my code completed successfully bt which doesnt show the result bcz of this warning message....can anyone help me??????declare getcur cursor for select sd.roll_no from student_details sd where sd.degree_id = @degree_id AND sd.branch_id = @branch_id AND sd.course_id = @course_id AND sd.batch = @batch open getcur FETCH NEXT FROM getcur INTO @roll_no WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #temp SELECT sd.reg_no, sd.student_name, bd.branch_name, cd.course_name, r.noof_semester, max(isnull(sm.sem_attended,0))as sem_attended, @is_completed as is_completed, rs.sub_code, rs.sub_name, case when sm.int_mark_obtained is null then 0 else @int_mark_obtained end, case when sm.ext_mark_obtained is null then 0 else @ext_mark_obtained end, isnull(sm.int_mark_obtained,0)+isnull(sm.ext_mark_obtained,0)as total_marks, case when sm.exam_status='p' then 'PASS' when sm.exam_status= 'f' then 'fail' end exam_status FROM student_details sd INNER JOIN student_marks sm ON sd.roll_no = sm.roll_no INNER JOIN regulation_subject rs ON rs.regulation_sub_id = sm.regulation_sub_id INNER JOIN regulation r ON r.regulation_no= rs.regulation_no INNER JOIN course_details cd ON cd.course_id = sm.course_id INNER JOIN branch_details bd ON bd.branch_id=sd.branch_id WHERE @noof_semester =(select max(@sem_attended) from student_marks sm where sm.roll_no=@roll_no) AND sd.roll_no = sm.roll_no and @batch=2007 and @course_id=99 group by sd.reg_no, sd.student_name, bd.branch_name, cd.course_name, r.noof_semester, rs.sub_code, rs.sub_name, sm.int_mark_obtained, sm.ext_mark_obtained, sm.exam_status if @noof_semester=max(@sem_attended) begin set @is_completed='yes' end else begin set @is_completed='no' end fetch next from getcur into @roll_no end select *from #temp order by reg_no close getcurdeallocate getcurdrop table #tempTue, 22 Jan 2013 04:27:11 GMTnitha jenhttp://www.sqlservercentral.com/Forums/Topic532612-8-1.aspx[quote][b]dineshvishe (7/26/2012)[/b][hr]But we want use in view .is that possible ??[/quote]Yes, of course.Just use SET ANSI_WARNINGS OFF in procedures which select from the view.Wed, 15 Aug 2012 22:21:38 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic532612-8-1.aspxBut we want use in view .is that possible ??Thu, 26 Jul 2012 01:42:02 GMTdineshvishehttp://www.sqlservercentral.com/Forums/Topic532612-8-1.aspxthank you for the clarification, I appreciate it!Tue, 15 Jul 2008 15:00:45 GMTAngelindiegohttp://www.sqlservercentral.com/Forums/Topic532612-8-1.aspxThis is not an error message. SS2K is simply telling you that it ignore NULL values when performing an aggregate operation on records.Such as5, NULL, 7SUM will produce the result 12 WITH the warning message.Anything added, subtracted, etc. to NULL yields in NULL. 5 + NULL + 7 --> NULL.You could filter out the NULL values before calling the aggregate.RegardsTue, 15 Jul 2008 14:35:25 GMTJ-440512http://www.sqlservercentral.com/Forums/Topic532612-8-1.aspxthank you both!! I am off to fix the problem!! Have a great week! :hehe:Mon, 14 Jul 2008 08:06:32 GMTAngelindiegohttp://www.sqlservercentral.com/Forums/Topic532612-8-1.aspxor ...just replace your code toMIN(isnull(u.ITEM,0)) AS UnitItemMon, 14 Jul 2008 01:05:03 GMTkarthik Mhttp://www.sqlservercentral.com/Forums/Topic532612-8-1.aspxYou don't have to... SET ANSI_WARNINGS OFF... will eliminate this expected error message.Just to be sure about a couple of things, you might want to post the proc for a look-see...Fri, 11 Jul 2008 19:22:07 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic532612-8-1.aspxHi all! Happy Friday!ok, I came across a stored proc that is sending out this error when it runs:"Null value is eliminated by an aggregate or other SET operation."here is the existing code partial piece of code: MIN(u.ITEM) AS UnitItemnow the case here is that there is a NULL value in the column. What is the proper syntax for checking for nulls in this aggregate situation?? (I have tried a couple different ways and failed)thank you in advance!Fri, 11 Jul 2008 09:56:36 GMTAngelindiego