JDC573164

Hi all,



I came across this problem, and the most complete info about it I found was in


http://www.sharepointblogs.com/smc750/archive/2007/06/20/custom-cross-list-search-development-pitfalls-part-two.aspx


stating


<Where>

<Or>

<Eq>

<FieldRef Name="Customer_x0020_Age" />

<Value Type="Number">25</Value>

</Eq>

<Eq>

<FieldRef Name="Age" />

<Value Type="Number">25</Value>

</Eq>

</Or>

</Where>


The Problem with "OR" in WSS

The reason the above CAML query "where" will not return any results is that in order for an "OR" to return results in WSS the two columns must exist in the document library where the document is stored. For instance, if I want to query for documents in a site collection that have an "AccountType" = Consumer OR "LoanType" = Auto, the only documents that will be returned are documents that have both "AccountType" and "LoanType" columns defined in the document library. Documents that have only one of the columns defined in the document library will not be returned even if the criterion matches.

Does anyone know a workaround for this issue

Thanks,
JC



Re: SharePoint - Development and Programming Problem with "OR" in CAML Query

Chase M

If the column doesn't exist in some lists/libraries you can try setting the Nullable attribute on that FieldRef in your query.

So, if you know some lists/libraries do not contain the Customer_x0020_Age column change that FieldRef element like so:

<FieldRef Name="Customer_x0020_Age" Nullable="TRUE" />

This works ok for the most part, but depends on the column type. Try this on a column of type URL and you'll get a nice Sql Exception thrown back at you. See, to get the value for a URL column WSS needs the values from two columns in the database. When it dynamically constructs the T-SQL to query for that list and does UNION with a table that it thinks is going to have just one column always containing NULL as its value, well... UNION demands that all tables have the same amount of columns...

The workaround (I've found) in that case is to add a hidden field of the same name and type to the lists/libraries that are lacking that field. Because its hidden it doesn't bother the user, and because you no longer have to mark the field as nullable in your query the query actually works.

see http://msdn2.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery.aspx

for some other field type issues when querying across lists

Hope that helps,

Chase