I have been working on some Definition Queries for layers in ArcGIS (10.2.0.3348). The layers connect to data in a File Geodatabase. Now I have attempted to implement the following query (1), that returns 3051 records when I open the attribute table. However, when I run queries 2 and 3, I get 3280 and 2 records respectively. I would have expected the difference in the number or records between query 1 and 2 to be 2 records, as per the result of query 3, but obviously, this is not the case. I guess my understanding of the processing of these queries by the database engine is wrong, and especially the "AND NOT" part of it, but can someone give a better insight why these 3 queries don't match up in terms of number of records?
Also note the result of query 4: it seems the database engine ignores the extra statements against osm_level, osm_layer and osm_location, as the number of records in query 4 is equal to the difference in query 1 and 2?
What am I missing? I have also tried to restructure the query to the one under 5, but that returns the same number of records as query 1…
I realize I could put the required values for "railway" in the IN statement, instead of trying to exclude with the "AND NOT", but I have some specific reason to maintain this structure (catch all for unknown values that get symbolized by the "all other values" item in an ArcGIS layer), and need to live within the limitations of the Definition Query and Layer framework of ArcGIS.
(I would have loved to post this question on the new GeoNet as well, but just can't work with all the clutter…)
QUERY 1:
(railway NOT IN (Null,'') AND osmSupportingElement = 'no') AND NOT
(railway IN ('platform') AND (osm_level LIKE ('%-%') OR osm_layer LIKE
('%-%') OR osm_location IN ('underground','underwater')))
Number of returned records: 3051
QUERY 2:
railway NOT IN (Null,'') AND osmSupportingElement = 'no'
Number of returned records: 3280
QUERY 3:
(railway IN ('platform') AND (osm_level LIKE ('%-%') OR osm_layer LIKE
('%-%') OR osm_location IN ('underground','underwater')))
Number of returned records: 2
QUERY 4:
railway IN ('platform')
Number of returned records: 229
QUERY 5:
((railway NOT IN (Null,'') AND osmSupportingElement = 'no') AND NOT
((railway IN ('platform') AND osm_level LIKE ('%-%')) OR (railway IN
('platform') AND osm_layer LIKE ('%-%')) OR (railway IN ('platform')
AND osm_location IN ('underground','underwater'))))
Number of returned records: 3051
Best Answer
The reason they don't add up is that in query 1, you have a condition Query 1: (this) AND NOT (that).
Queries 2 and three have only one condition Query 2: (this) Query 3: (that)
You undoubtedly have some records where (this) is true AND (that) is true and also where they are both false.
Try modifying query 1 (take out NOT)
and see what you get. Also try breaking the queries into smaller pieces and you may come by the answer on your own (see my comment).