Filtering Feed Using Where Clause - Not Working

[ArmstKP]

Pictured below, the where clause exception will not work if I use AND.

If I use just: makemodel = '%Ford%' it will work.  I read in the documentation that AND is recognized SQL-92 that Velocity will work with.

What gives?

[PeterNasuti]

Thanks for posting - I am not seeing a screenshot, can you provide a screenshot for context? Is this with a Feature layer feed/source where clause? Are you querying a Velocity layer or an ArcGIS Online or ArcGIS Server service?

[ArmstKP]

@PeterNasuti This is a hosted table join view.

[PeterNasuti]

When configuring the "Where clause" parameter for an ArcGIS Velocity feature layer feed or source, Velocity is sending the exact query to the REST API query operation for the referenced service. You mentioned that you are configuring a Feature layer feed to reference an ArcGIS Online hosted feature service. Therefore I would encourage in such a circumstance testing your expression at the REST endpoint of the ArcGIS Online hosted feature service as Velocity simply hands the expression along to query at the REST call.

In testing on my end, I would also expect the % characters to work with the "LIKE" operator rather than "=" operator.

I confirmed success in testing with an expression such as incidentnm LIKE 'baltimore%' AND inspector = 'peter'

[JohannesLindner · Accepted solution]

SQL uses logical operators. The operator AND means "Both values have to be true". So in your query, you search for features where makemodel is both Ford and Toro. This is impossible, so the query layer will be empty.

You should use the logical OR, which means "At least one of these has to be true".

Also, not sure if this applies to querying feature services, but in "normal" SQL, % is a placeholder/wildcard for seaching text with the operator LIKE.

makemodel LIKE '%Ford%' will find features with "Ford Fiesta", "Ford GT", and "Ford Mustang". makemodel = '%Ford%' should only find rows with the actual field value %Ford%.

[ArmstKP]

@PeterNasuti @JohannesLindner Geez.  My error.  Thanks for pointing out my bone-headed oversight.  I changed my where clause to this and it worked.  Thank you!!:

makemodel LIKE '%Bobcat%' OR makemodel LIKE '%CAT%' OR makemodel LIKE '%Ford%' OR makemodel LIKE '%Holder%' OR makemodel LIKE '%Holland%' OR makemodel LIKE '%Toro%' OR makemodel LIKE '%Ventrac%'

[ArmstKP]

@PeterNasuti @JohannesLindner I just checked my real-time outputs that are using this feed with the where clause.  Values are still coming through that shouldn't be.  I am still receiving values that are "Kubota", even though that is not in the clause.....

[PeterNasuti]

When a WHERE clause is configured for a feature layer feed or source, Velocity simply passes the specified clause along to the REST API query operation when it repeatedly queries the feature layer at the designated polling interval. If you are not seeing success with this and have confirmed that these are new features being written to the output (rather than older features that were previously written), I would suggest logging a support ticket with Esri Support Services as this would require a call and screenshare to review your configuration.

Additionally, I would encourage you to test your WHERE clause at the REST endpoint of the ArcGIS Online hosted feature layer to ensure that only the desired features are returned by your where clause expression directly at that query endpoint. This will help isolate if there is an issue independent of ArcGIS Velocity, or specific to ArcGIS Velocity.