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

[craigward]

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

 

[markros]

Remove this part from your query

where not edrs is null

it should have been

where Edrs IS NOT NULL, but better to not use it at all here.

Also if you run the select statement alone (remove the insert part for now) would you get the same error?

 

[craigward]

Thanks for your response.

I did try both variations, is not null and also removed it, problem when i remove it it i get one null result appear because not every record has an EDRS number and i need the EDRS number to be the uniqueid in the other table (wce_contact) which can't have a uniwueid as a null... catch 22.

I have looked at all the records and thought of populating the EDRS manually but i'll be there forever :-(

If i run the select statement alone i get no errors and i see the results but just one null edrs value which is why i added the edrs is not null which visually works but must break the query when inserting.

anymore thoughts would be great.

 

[gmmastros]

try this:

[!]set ansi_warnings off[/!]

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

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Try slightly different version here:

select EDRS As Uniqueid, 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(ISNULL(Emp_Address_pcode1 + ' ','') + isnull(Emp_Address_pcode2,'')) As Postcode, 'Company' from wce_ilr where Edrs is NOT null group by edrs

 

[craigward]

Thanks both of you for helping out. Unfortunatly neither solutions worked. It thinks there is a duplicate in there and terminates teh query.

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

The crazy thing is, it gives the same error if i use:

set ansi_warnings off

INSERT INTO wce_contact (UNIQUEID, record_type)
select distinct EDRS, 'company' from wce_ilr where Edrs is NOT null group by edrs

 

[gmmastros]

Well.... at least we got a little further.

If I had to guess, I would say that UNIQUEID is a primary key or it has a unique constraint on it. So.... there must be an EDRS that you are trying to insert already in the wce_contact table.

If my suspicion is correct, then the following query will return at least one row.

Select *
From   wce_contact
       Inner Join wce_ilr
         On wce_contact.uniqueid = wce_ilr.edrs

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Is your table empty before the insert? Perhaps you already have EDRS the same as in your wce_ilr in wce_contact table.

You may try then

INSERT INTO wce_contact (UNIQUEID, record_type) select distinct W.EDRS, 'company' from wce_ilr W LEFT JOIN wce_contact E ON W.Edrs = E.Edrs where W.Edrs is NOT null and E.Edrs IS NULL

 

[craigward]

Ok, you got it!! Well done and thanks, I am using data from a college and in this table there are learners, employers and courses, each type have a uniqueid's ish... on 4 rows they had used the same edrs values for 2 learners learner codes which should have been unique.

Thanks a lot for your help that was doing my head in but i learned two valuable lessons.

college data is not great and look at the more logical options first

thanks again

 

[gmmastros]

I'm glad you were able to get your problem solved.

college data is not great

It does not matter where the data is coming from, you should always treat it with a fair amount of suspicion.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom