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.

[JaredWhite]

I assume from your code that you're trying to make the field in your sql where clause variable. I had this problem last week and my solution was to add a few variables to the string itself instead of rebuilding the string for each instance. Something like this.

            searchURL = /*"your_service_url"*/;
            searchField = /*"your_search_field"*/;
            searchOutFields = /*"list_of,outfields,you_want,returned"*/;                
            

            ESRI.ArcGIS.Client.Tasks.Query query = new ESRI.ArcGIS.Client.Tasks.Query()
            {
                ReturnGeometry = true,
                OutSpatialReference = TabletMap.SpatialReference,
                Where = string.Format("{0} LIKE '%{1}%'", searchField , FindText.Text),
                OrderByFields = new List<OrderByField>() 
                     { new OrderByField(searchField , SortOrder.Ascending) }
            };

            query.OutFields.Add(searchOutFields);
            QueryTask queryTask = new QueryTask(searchURL);

[DmitriiIgnatiev]

Are you able to execute a similar SQL against your layer table somewhere outside ArcGIS Server environment but using same credentials? How many records do you have in both cases? Is it imaginable that SUBSTR fails because of some reason (null or too short value) and the server just swallows that? Any record in server log? Any warning from database server?

[PreetiMaske]

      
 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.

It may be related to maximum records returned by the server. For e.g if max # of features returned is set 1000 for the service and query leads to more than 1000 features then only 1000 will be drawn/rendered. An easy way to test would be to use the SQL where clause using service rest end point and see how many records you get.

[MosesAsuquo]

Are you able to execute a similar SQL against your layer table somewhere outside ArcGIS Server environment but using same credentials? How many records do you have in both cases? Is it imaginable that SUBSTR fails because of some reason (null or too short value) and the server just swallows that? Any record in server log? Any warning from database server?

Yes, I am able to run the exact SQL generated by the function in oracle and i get records returned. Again, in some instances, the number of records found in the oracle database matches the number of features returned by the Silverlight API QueryTask. It matches more so when the number of records returned by the database is small, say 50 or 8. But when it is in the thousands it becomes clear that the API isn't returning the exact number of features the really does exist in the layer. In one instance, the database (Toad) returned 2,891 rows and the API returned only 500 features which is considerably off. One plausible explanation could be that the SUBSTR() fails on certain records when the FacilityID is NULL but if that's the case shouldn't it also fail in the SQL client when the same SQL statement is been executed? The records returned by SQL Client (Toad) all have FacilityID(s) assigned to them. There are no errors from the database server. I would presume if there was an error it would be in the Application Server log. In the application server log, i see the GET request been sent by the ArcGIS QueryTask on the layer rest endpoint and it returns with no visible errors or warnings.

2012-08-03 14:32:34 192.168.1.15 GET /ArcGIS/rest/services/whatever/whatever/MapServer/62/query returnGeometry=true&spatialRel=esriSpatialRelIntersects&where=PIPEMATERIAL+%3d+'WHATEVER'+AND+WIDTH+IN+(3%2c4%2c6%2c8%2c10%2c12%2c14%2c15%2c16%2c18%2c21%2c24%2c25%2c27%2c30%2c36%2c42%2c54%2c60%2c66%2c72)+and+status+%3d+1+&outFields=*&f=json& 80 - 192.168.43.90 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+6.1;+WOW64;+Trident/4.0;+SLCC2;+.NET+CLR+2.0.50727;+.NET+CLR+3.5.30729;+.NET+CLR+3.0.30729;+Media+Center+PC+6.0;+.NET4.0C;+.NET4.0E) 200 0 0 2813

I'm still racking my head to findout a logical explanation. I just found out that in cases where the SUBSTR() is not been used, in order words, where the selection condition doesn't require a SUBSTR() concatenated in the SQL statement (though the AND(s) logical conditions are still included; I believe QueryTask has no problems with logical or relational operators from reading it's API documentation) the same behavior is happening. The number returned by the QueryTask doesn't always match what is returned by the database even when i can see the unreturned line features, they are spatialized, and they are on the map and they do have FACILITYID(s) assigned to them.

[MosesAsuquo]

It may be related to maximum records returned by the server. For e.g if max # of features returned is set 1000 for the service and query leads to more than 1000 features then only 1000 will be drawn/rendered. An easy way to test would be to use the SQL where clause using service rest end point and see how many records you get.

Good point. I thought the same much earlier believing it was a problem with the SUBSTR so i decided to run the WHERE statement on the database directly and get all the records from the database then from these returned results i looped through to concatenate the FacilityIDs which are unique identifier of each database record. I  then created an IN CLAUSE using the command delimited facility IDs.

Well that approach did not work because i later realized that there was a limitation in Oracle of 1000 values in an IN CLAUSE. One suggestion when to break the IN CLAUSE by one thousands and apply a UNION on each set which i learn reduces performance significantly. So i decided not to go th IN CLAUSE route and stick with the little complex query route.

I would want to believe the the complex query route has no ORACLE limitations in the number of records to be returned. The IN CLAUSE route showed limitions not only in the API but also in Toad or SQLClient which means it's a legitimate ORACLE error or restriction. The complex query route runs fine and returns results in SQLClient or Toad so i will presume there are no restrictions from ORACLE; it should run like any other normal sql statement. I need to figure this out. It's mind bogling. Thanks for the suggestion. If you have other helpful hints please don't hesitate.

[PreetiMaske]

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.

[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!