Need help with a definition query

[TheoFaull]

I have a point shapefile with 2 columns. Each column contains numbers relating to price increases of a service. One column is how much the price has risen by (£) and the second column is what percentage it has risen by, compared to the original price.

I want the layer in ArcMap to only display points where the price rise is above 10 (£) AND the percentage is over 50 (%).

Using the definition query tab, perhaps it would look like:

"PRICE_RISE" > 10 AND "PERCENTAGE_RISE" > 50

however, this returns an invalid sql statement...

[DarrenWiens2]

Are both fields numeric, or is it possible one or both are text? Did you use the definition query buttons to fill it it, or did you type it?

[TheoFaull]

Darren Wiens​ The price rise field is a 'double' and the percentage rise is a 'string'. The fields are  not filled by a query (I didn't know this was possible?). The shapefile is actually a an export from another shapefile which had a CSV file joined to it.

[DarrenWiens2]

I meant, you should open the query builder when you build a definition query and use the buttons to make the query. This will help you use proper quotes around field names and field values.

[MicahBabinski]

Hi Theo,

You may be able to ditch the double quotes around field names. Also, (as Darren Wiens​ mentioned), those could be text fields. You could use CAST to convert those values to integer.

Micah

[JoshuaBixby]

The OP is working with shape files, or at least indicates so, which means CAST won't work.

[TheoFaull]

OK I did it with:

"PRICE_RISE" > 10 AND "PERCENTAGE_RISE" > '50'

The string field needed single quotes around the value, but the double field didn't. I didn't realise different field stypes required different levels of quotation marks. I wonder if there is an ESRI help article somewhere that explains it all!

[DanPatterson_Retired]

There are certainly references in the help file

SQL reference for query expressions used in ArcGIS—Help | ArcGIS for Desktop

and they contain references as to how the field names are to be treated depending upon whether shapefiles or gdb's are being queried and how to handle numbers and text

[WesMiller]

You may want to check your results. The string field will not produce the results you.d expect. For example you asked for "PERCENTAGE_RISE" > '50' what you are probably getting is 50,51...6,60,61...7...

[TheoFaull]

ok, but why would 'greater than 50' return results like 6 or 7?

Checking my data, it seems to have only values greater than 50, but maybe I got lucky and didn't have any values as 6, 7, 8, or 9...