March 2, 2005 at 4:15 pm
Could someone give me some help on inserting the field PRContactAssoc.Role with this script? (Error received is below)
The data is "GROUP BY PRContactAssoc.WBS1, Contacts.ClientID" because these two fields together create the primary key for the PRClientAssoc table. Thanks a LOT! - S
INSERT INTO PRClientAssoc
SELECT
PRContactAssoc.wbs1, Contacts.ClientID, 'N', null, Null, PRContactAssoc.Role, 'N', 'ADMIN', Null, 'ADMIN', Null
From PRContactAssoc
Inner Join
Contacts
on
PRContactAssoc.ContactID = Contacts.ContactID
WHERE
Contacts.FirstName not like 'Office' and
not exists
(select 'x' from PRClientAssoc where
PRContactAssoc.WBS1 = PRClientAssoc.WBS1 and
Contacts.ClientID = PRClientAssoc.ClientID)
GROUP BY PRContactAssoc.WBS1, Contacts.ClientID
Server: Msg 8120, Level 16, State 1, Line 1
Column 'PRContactAssoc.Role' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
March 2, 2005 at 4:32 pm
If there is only one PRContactAssoc.wbs1 for each Contacts.ClientID/PRContactAssoc.Role then GROUP BY PRContactAssoc.WBS1, Contacts.ClientID, PRContactAssoc.wbs1
To check it is the case try
SELECT
PRContactAssoc.wbs1, Contacts.ClientID, PRContactAssoc.Role, count(*) From PRContactAssoc
Inner Join
Contacts
on
PRContactAssoc.ContactID = Contacts.ContactID
WHERE
Contacts.FirstName not like 'Office' and
not exists
(select 'x' from PRClientAssoc where
PRContactAssoc.WBS1 = PRClientAssoc.WBS1 and
Contacts.ClientID = PRClientAssoc.ClientID)
GROUP BY PRContactAssoc.WBS1, Contacts.ClientID,PRContactAssoc.Role
having count(*) > 1
If not, you have problem anyway.
March 2, 2005 at 9:29 pm
So . .. if I
GROUP BY
PRContactAssoc.WBS1, Contacts.ClientID, PRContactAssoc.Role
Will this mess up the PK which is comprised of the first 2 of 3 in the GROUP BY? When I try adding the role as stated, I receive a PK violation. Thanks again! --Shane
March 2, 2005 at 10:36 pm
Correction - it will work for the first round (where firstname is like "Office"), but then the second round (where firstname not like "office") - I receive the PK violation. -Shane
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply