Warning: Null value is eliminated by an aggregate or other SET operation

Question

Warning: Null value is eliminated by an aggregate or other SET operation

sc-w

Hall of Fame

Points: 3667
More actions
May 5, 2009 at 5:07 am

#384504

Hi,
I am getting this SQL error:
Msg 2627, Level 14, State 1, Line 1
Warning: Null value is eliminated by an aggregate or other SET operation
I am trying to insert data from one table into another the first table does have duplicate records but using the max() function they have all been ruled out and i am displaying 12000 individual records when i check the select part of my query. I have check and there are no nulls either.
Any other reason i could be getting this error? Thanks for any help.
INSERT INTO wce_contact (UNIQUEID, EDRS, Company, phone,
address1, address2, city, county, postalcode, record_type)
select EDRS As Uniqueid, max(EDRS) As EDRS, max(Employer_name) As
Employer_name, max(emp_phone) As emp_phone, max(Emp_Address_1) As
Address1, max(Emp_Address_2) As Address2, max(Emp_Address_3) As
Address3, max(Emp_Address_4) As Address4,
max(Emp_Address_pcode1)+' '+ max(Emp_Address_pcode2) As
Postcode, 'Company' from wce_ilr where not edrs is null group by
edrs order by edrs

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Any other reason i could be getting this error?

At least one of the columns in an aggregate has a null in some row. For example, if for any row, the condition "Employer_name is null" is true, then the informational message is provided.

SQL = Scarcely Qualifies as a Language

sc-w Hall of Fame Points: 3667 More actions · Answer 2

Thanks for the reply. I read up on that but two things don't makes sense on that being the issue. Firstly i used the same query from data in the same table using a different id field and that worked fine and some fields had null values.

And secondly i tried a distinct function using just this and got a similar error.

INSERT INTO wce_contact (uniqueid)

select distinct EDRS from wce_ilr where edrs is not null

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'pk_wce_contact_uid'. Cannot insert duplicate key in object 'dbo.wce_contact'.

HanShi SSC-Dedicated Points: 33507 More actions · Answer 3

sward (5/5/2009)
Hi,
I am getting this SQL error:
Msg 2627, Level 14, State 1, Line 1
Warning: Null value is eliminated by an aggregate or other SET operation

This error is generated by the "SELECT ... FROM wce_ilr ..." part of the query and not by the INSERT part. It is generated because one or more of the columns, where you take the MAX from, contains a NULL value. This is what Carl told you in his reply.

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'pk_wce_contact_uid'. Cannot insert duplicate key in object 'dbo.wce_contact'.

This error is generated because you want to insert a value into column "uniqueid" that allready exists in that column. A constraint (pk_wce_contact_uid) is applied on column "uniqueid" that prevents duplicate values. This error is not related to the first error.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

vikram.gharge SSC Rookie Points: 38 More actions · Answer 4

just add ISNUll condition where u have used Aggreate funtion

means Select SUM(ISNULL(TotalAmount,0)) from tbl_XYZ