Apex Code Development You need to sign in to do that
Don't have an account?
garyb Retrieve cases where OwnerID = user and user is Case Team Member
Hi all, hopefully a straightforward one!
I want to retrieve cases where the user is the owner and the user is also a case team member. I'm trying to do something like:
Select ID From Case where OwnerID = 'someUserID' and OwnerID in (select MemberID from CaseTeamMember)
I'm running this in the Force.com IDE and I'm getting the following message:
"The selected field 'MemberID' in the subquery and the left operand field in the where expression in the outer query 'OwnerID' should point to the same object type."
MemberID can be a reference to a Contact or a User, whereas OwnerID can be a reference to a Group or a User - I suspect this is what's causing the problem?
Any advice gratefully received :)
The first thing that pops out at me is that the subquery is not very specific. I think you need to specify a 'where ...' clause to narrow down the subquery a bit. It looks like what you have now will return all of the MemberId's from CaseTeamMember which does not seem to make sense.
Hi Technogeek,
I did try a where clause in the sub-query before I posted i.e.
Select ID From Case where OwnerID = 'someUserID' and OwnerID in (select MemberID from CaseTeamMember where MemberID = 'someUserID')
However, I still get the same error message. I'm pretty sure it's saying that the sub-query returns IDs that aren't the same as what we are comparing with - which is only half true...
Gary,
Looking at the subquery that you specified
That does not really seem like it will do much. You are requesting that it return the MemberId where MemberId = UserId, which appears to be redundant.
I have not played with Case Teams and don't have any defined, so I can't really test to see what you are trying to do, so I don't think I can be much more helpful. Sorry.
Apologies for not replying on this.
Technogeek, you are right, the SOQL query says "Give me the Cases where this ID is the OwnerID and where that user ID also appears in the CaseTeamMember table". It would possibly make sense if we had a case ID in the query as well (in the main query and sub-query). However, I was looking more for a "Give me the cases belonging to this user and where the user is a case team member" - I was definitely barking up the wrong tree.
Anyway, since my last post, things changed - it turns out it was supposed to be "display cases where user is the owner OR (not "and"!) a case team member" and unfortunately you can't use a sub-query in the where clause if you're using "or". I then tried querying CaseTeamMember instead but I was using StandardSetController for pagination and this does not support CaseTeamMember. So instead, I decided to query the CaseTeamMember table first and use the results in my query to the Case table. Not as neast as I'd have hoped, but it works.