I wrote some CAML Queries to get data from a List that has lookups on another List. All is working fine as long as I do not reference a Lookup Field which points to a Calculated Field.
I tried different types in the value field, but it just doesn't work. When I replace the FieldRef with another field from the list, it works as excpected. Why can't I use a lookup to a calculated Field in a Query?
Definition of the to-be-queried Lookup Field in List A
<Field Name="Mitarbeiter" ShowField="Search" ID="{...}" DisplayName="Mitarbeiter" Type="Lookup" List="Lists/Mitarbeiter" />
The referenced calculated Field in List B
(basically displays "Lastname, Firstname" in this example)
<Field Name="Search" ID="{...}" Hidden="FALSE" DisplayName="Search" Type="Calculated" ResultType="Text" ReadOnly="TRUE">
<FieldRefs>
<FieldRef Name="Title" />
<FieldRef Name="Vorname" />
</FieldRefs>
<Formula>=Title & ", " & Vorname</Formula>
</Field>
and the CAML Query (Field Mitarbeiter
Points to a calculated Field in another List)
<Where>
<Contains>
<FieldRef Name="Mitarbeiter" />
<Value Type="Text">Name of person</Value>
</Contains>
</Where>
The Request crashes and I find several entries in the SP Logs
System.Data.SqlClient.SqlException: Argument data type sql_variant is invalid for argument 1 of like function.
Unknown SQL Exception 8116 occurred. Argument data type sql_variant is invalid for argument 1 of like function.function.'