Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I have two custom lists.

list 1 having following columns:
ID (consider as PK)
title

list 2 having following columns
NID (lookup column)
title

I'm using spquery.joins like below

<Joins> 
  <Join Type='INNER' ListAlias='list2'> 
    <Eq> 
      <FieldRef Name='ID' RefType='Id' /> 
      <FieldRef List='list2' Name='NID' />  
    </Eq> 
  </Join> 
</Joins>

I'm defining projected fields as well like below

"<Field Name='NID' Type='Lookup' List='list2' ShowField='NID'/>";

I don't know what is wrong with this but whenever I try I get following exception

Value doesn't fall within the expected range

share|improve this question
add comment

4 Answers

it is most likely how you are getting the list that is causing the issue.

Can you paste the code that calls this CAML query just to see? But first off are you calling the results via the InternalName?

But I believe the answer is:

results[results.Fields.GetField(columnName).InternalName]

OR

You have the ViewFields property on, which will also cause this error with this code. The code looks fine however as it is.

share|improve this answer
    
i m not using view fields property –  Muhammad Adnan Aug 28 '12 at 10:11
    
i m using joins first time in spquery... so i just copied some code from internet article. can u pls tell me in my code mentioned above. what is this line means <FieldRef Name='ID' RefType='Id' /> –  Muhammad Adnan Aug 28 '12 at 10:20
    
It is the field you are referencing for the query, ie the filter in this case the comparison for the join. Field ref : msdn.microsoft.com/en-us/library/ms442728.aspx If you could link the article you got this from, and paste your code we could have a clue where this is going wrong. I am guessing it is the way the values are being pulled out. –  Hugh Wood Aug 28 '12 at 10:32
    
"<Where><And><Eq><FieldRef Name=\"NomineeStatus\" /><Value Type=\"Text\">NewRecord</Value></Eq><And><Geq><FieldRef Name=\"Modified\" /><Value Type=\"DateTime\">2012-04-01T00:00:00Z</Value></Geq><Leq><FieldRef Name=\"Modified\" /><Value Type=\"DateTime\">2013-03-01T00:00:00Z</Value></Leq></And></And></Where>" –  Muhammad Adnan Aug 28 '12 at 10:46
    
"<Joins><Join Type='INNER' ListAlias='TaskList'><Eq><FieldRef Name='ID' RefType='Id' /><FieldRef List='TaskList' Name='NomineeID' /></Eq></Join></Joins>" –  Muhammad Adnan Aug 28 '12 at 10:47
show 3 more comments

An example of JOIN:

SPQuery oQuery = new SPQuery();
StringBuilder sbJoin = new StringBuilder();
StringBuilder sbProj = new StringBuilder();

oQuery.Query = "<OrderBy><FieldRef Name='Title' /></OrderBy>";

using (SPSite siteCollection = new SPSite("http://localhost/"))
{
    using (SPWeb web = siteCollection.OpenWeb())
    {
        SPList oList = web.Lists["List1"];
        SPListItemCollection items = null;

        sbJoin.Append("<Join Type='INNER' ListAlias='List2'>");
        sbJoin.Append("<Eq>");
        sbJoin.Append("<FieldRef Name='List2' RefType='Id'/><FieldRef List='List2' Name='ID'/>");
        sbJoin.Append("</Eq>");
        sbJoin.Append("</Join>");


        sbProj.Append("<Field Name='List2NID' Type='Lookup' List='List2' ShowField='NID'/>");
        sbProj.Append("<Field Name='List2ContentTypeId' Type='Lookup' List='List2' ShowField='ContentTypeId' />");

        oQuery.ViewFields = "<FieldRef Name='Title'/><FieldRef Name='List2NID'/>";
        oQuery.Joins = sbJoin.ToString();
        oQuery.ProjectedFields = sbProj.ToString();

        items = oList.GetItems(oQuery);

        if (items.Count > 0)
        {
            foreach (SPListItem item in items)
            {
                Console.WriteLine("Title: {0}, List2 NID: {1}",
                        item["Title"],
                        item["List2NID"]);
            }
        }
    }
}

Recalling that is only possible using simple join with columns as text, number and datetime columns ... with another type could give this same error

share|improve this answer
add comment

I must say that in my experience using CAML for Joining falls very quickly short in its usage. Not sure if this is answering your question, but in my experience, relying on a Content type used in both targeted lists, than using a SPSiteDataQuery (many example available on the web, e.g. http://www.c-sharpcorner.com/uploadfile/Roji.Joy/cross-site-list-query-using-spsitedataquery-in-sharepoint-2010/) to query for the Content type (e.g. using Content type Id as one of the filter conditions) + limiting which lists are actually participating (using the Lists property -http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery.lists.aspx), to which you would get the benefit of being able to state (via ViewFields) which fields to be retrieved - for performance reasons, and same obvious features (WHERE, ORDER, LIMIT, etc. clauses) resulted in fastest, more predictable.

Add to this the fact that you can also target cross sub-sites these lists, and combine with caching of results via CrossListQueryCache (see here a comparison http://gadekarsatish.blogspot.co.at/2013/03/query-sharepoint-list-spquery-vs.html) - i would say this gives you the answer!

share|improve this answer
add comment

I think you have mistake in this line <FieldRef Name='ID' RefType='Id' />.

In this line "Name" attribute contain with LookuptypeColumn of list. See this url: http://sharepointfordeveloper.blogspot.in/2011/10/step-by-step-spquery-list-joins.html

share|improve this answer
    
i have two lists.. list 1 contains ID column and list 2 contains NomiID column (which is lookup column of list 1 ID column). i need to join using these two columns, i m joining like this now oQuery.Joins = @"<Joins><Join Type='INNER' ListAlias='TaskList'><Eq><FieldRef Name='NomiID' RefType='ID' /><FieldRef List='TaskList' Name='NomiID' /></Eq></Join></Joins>"; oQuery.ProjectedFields = @"<Field Name='NomiID' Type='Lookup' List='TaskList' ShowField='Title' />"; oQuery.ViewFields = @" <FieldRef Name='Title' />"; pls see whats wrong –  Muhammad Adnan Aug 29 '12 at 5:54
    
see this link you getting better idea: blogs.msdn.com/b/kaevans/archive/2012/01/20/… –  Ravi Suthar Aug 29 '12 at 6:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.