Use column comparison in queries

In the Common Data Service, users can perform a column comparison for the following condition operators using FetchXML, Web API, or the SDK API:

  • Equal
  • NotEqual
  • GreaterThan
  • GreaterEqual
  • LessThan
  • LessEqual

This will allow the comparison of an attribute against a specific value and return all found records, or allow the comparison of two attributes to return all records with a matching value.

Limitations

Listed below are the limitations for the current Common Data Service column comparison support.

  • You can only compare columns within a single entity
  • Only two columns may be compared at a time.
  • Multi-value condition operators are not supported (i.e., "in").
  • Extended condition operators are not supported (i.e., "creditlimit > spends+1000").
  • Incompatible attribute comparison is not supported. For example, "int vs. int" attributes is a valid comparison but "int vs. string" attributes is not a valid comparison.

Column comparison using FetchXML

The following example shows how to compare columns using FetchXML:

<fetch>
  <entity name='contact' >
    <attribute name='firstname' />
    <filter>
      <condition attribute='firstname' operator='eq' valueof='lastname'/>
    </filter>
  </entity>
</fetch>

For FetchXML requests, a new node valueof has been added inside the condition node. The valueof tag is used to identify the attribute that is being compared to the selected attribute. In the above example, the 'firstname' column is being compared against the 'lastname' column and will return any records that contain the same value across both attributes.

See Also

Use FetchXML to construct a query
Column comparison using the Web API
Column comparison using the SDK API