SUBSTR( ) IN QUERYTASK WHERE CLAUSE

MosesAsuquo

      
 private void locateFeature(Selection sel)
        {
            string serviceURL;

            if (GravityMain.Target == PipeQueryBuilder.AddIns.MyDatabaseService.Table.SSGRAVITYMAIN_MV)
                serviceURL = "http://whatever/";
            else if (GravityMain.Target == PipeQueryBuilder.AddIns.MyDatabaseService.Table.SSPRESSURIZEDMAIN_MV)
                serviceURL = "http://whatever/";
            else if (GravityMain.Target == PipeQueryBuilder.AddIns.MyDatabaseService.Table.SWGRAVITYMAIN_MV)
                serviceURL = "http://whatever/";
            else
                serviceURL = "http://whatever/";

            // Query task initialization.
            QueryTask queryTask = new QueryTask(serviceURL);
            queryTask.ExecuteCompleted += QueryTask_ExecuteCompleted;
            queryTask.Failed += QueryTask_Failed;

            // Query task parameters. Return geometry, 
            Query query = new Query();
            query.ReturnGeometry = true;
            query.OutFields.Add("*");

            query.Where = constructWHERECLAUSE(sel);
            queryTask.ExecuteAsync(query);
        }

       public string constructWHERECLAUSE(Selection sel)
        {
            string whereclause = string.Empty;
            whereclause += "PIPEMATERIAL = " + "'" + sel.PipeMaterial + "'" + " AND WIDTH IN (" + GravityMain.conCatSizes(sel) + ")";
            whereclause += " and status = 1 ";

            if (sel.Owner != 0)
                whereclause += " and ptable.ownership = " + "'" + sel.Owner + "'";

            if (sel.SubType != 0)
                whereclause += " and subtype = " + "'" + sel.SubType + "'";

            if (sel.Rehab != "ALL")
                whereclause += " and rehabtype = " + "'" + sel.Rehab + "'";

            if (sel.SanitaryOption != SanitaryOptions.NOT_SANITARY)
                if (sel.SanitaryOption == SanitaryOptions.COMBINED)
                    whereclause += " and ((SUBSTR(FACILITYID, 19, 1) = 'C') or (SUBSTR(FACILITYID, 21, 1) = 'C'))";
                else if (sel.SanitaryOption == SanitaryOptions.SANITARY)
                    whereclause += " and ((SUBSTR(FACILITYID, 19, 1) = 'S') or (SUBSTR(FACILITYID, 21, 1) = 'S'))";
            return whereclause;
        }

Can I use QueryTask Where clause like in the code above? The above code works but it appears that it does not return all the features on the map that meets the SQL criteria. My only reasonable suspicion is that the QueryTask where clause gets confused when it sees the SUBSTR( ). I am not sure because the code runs fine with no exceptions or compile error. The only thing is that it does not return all the features that meets the SQL criteria and other times it does return all the features that matches the SQL criteria. Does anyone have any suggestion that might be helpful.  I need help with this.

Thanks in Advance.

MosesAsuquo

Let me explain again.I believe there is some confusion here. You may be wondering why Toad or SQL client can return 3500( just an example) records and the same query is returning only 500 when executed from the API. It is because the map service you are querying is set to return maximum of 500 features. Again this is a value that can be changed. You may ask the publisher of the service to change this value to a higher number but please keep in mind the performance

Here are couple examples:

1. Here is the URL from a 10.1 service http://serverapps101.esri.com/arcgis/rest/services/USA_Diversity/MapServer/3. Go to this URL and see MaxRecountCount property for this service is 1000. This means if the query returns are more than 1000 records using DB client records than irrespective of how many records are returned from the database ArcGIS Server service will return only 1000 ( using Rest endpoint or  by using Any API querytask). Please note, this number is set while publishing a service and may be changed.

At 10.1 this maxrecordcount property is exposed via Rest, so if you are using pre 10.1 server you may have to check this value using ArcGIS Server Manager or ArcCatalog.

Bottomline, its not the database, its not the API but actually it is a value set on the ArcGIS Server service which causes querytask to return 500/1000 or whatever number set on the service.

I am also attching two screenshots with two different values on two different services on the same server.

I hope this makes it clear. Please let me know if you have any questions.

Preetim,

You are right, we are heading in the right direction. While publishing the service in ArcCatalog the maxrecordcount property in the list of available parameters is set by default to 500. That is why every call to QueryTask on any layer returns no more than 500 and every sequential/consecutive calls (as in a loop) returns  500 plus whatever the next call returns (which may be 500 or less). I realized i couldn't make the changes using the service manager or ArcCatalog because we have ArcGIS 9.3. I made the changes on each layer by doing this: "For ArcGIS Server 9.3.1 and earlier, you must manually set the MaxRecordCount property in the configuration file at <ArcGIS install location>\Server\user\cfg\<configuration file name>.cfg. " and it works now. I've lost a week trying to solve this problem but it's worth it.

Thanks a lot!