arcgisarcgis-js-apiarcgis-server

Arcgis rest api service - query to check for numeric in a string field

I am using Arcgis rest api service query to return only records that have a numeric value in a string field. (actionid is the string field which can be null also) This is what I am trying so far for the WHERE clause in the ArcGIS REST Services for the feature class:

TEST 1

I tried the following for first test using DECIMAL:

CAST( (COALESCE(actionid , '0')) AS DECIMAL) > -10000000

This is the error I am receiving.

Error:

'where' parameter is invalid

TEST 2

I tried the following for second test using INTEGER:

CAST( (COALESCE(actionid , '0')) AS INTEGER) > -10000000

This is the error I am receiving.

Error:

Unable to perform query. Please check your parameters.

So far from my research, it looks like it should support all the above keywords since it uses SQL-92. What am I missing? Thank You.

Solution

  • I found the issue!

    The problem is that the CAST function will error out if it tries to cast a string to an integer:

    CAST( (COALESCE(actionid , '0')) AS INTEGER) > -10000000

    I was able to confirm this was the issue by running the same query but on a different field such as objectid that has no strings. This works fine:

    CAST( (COALESCE(objectid , '0')) AS INTEGER) > -10000000

    I also found out that ISNUMERIC does not work in ArcGIS rest api service:

    ISNUMERIC(actionid ) = 1

    As mentioned by cabesuon in the comments, even the following example does not work since it can include values such as a0:

    actionid like '%[0-9]%'

    So instead of using CAST or ISNUMERIC use the NOT LIKE statement. The following query is the solution and will return numeric values only as desired:

    actionid NOT LIKE '%[^0-9]%'