1

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

4 Answers 4

1

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.

8
  • i m not using view fields property Commented Aug 28, 2012 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' /> Commented Aug 28, 2012 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
    Commented Aug 28, 2012 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>" Commented Aug 28, 2012 at 10:46
  • "<Joins><Join Type='INNER' ListAlias='TaskList'><Eq><FieldRef Name='ID' RefType='Id' /><FieldRef List='TaskList' Name='NomineeID' /></Eq></Join></Joins>" Commented Aug 28, 2012 at 10:47
0

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

0

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

2
  • 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 Commented Aug 29, 2012 at 5:54
  • see this link you getting better idea: blogs.msdn.com/b/kaevans/archive/2012/01/20/… Commented Aug 29, 2012 at 6:01
0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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