Need help with a definition query

3327
15
06-22-2016 08:54 AM
TheoFaull
Occasional Contributor III

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...

0 Kudos
15 Replies
DarrenWiens2
MVP Honored Contributor

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
Occasional Contributor III

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.

0 Kudos
DarrenWiens2
MVP Honored Contributor

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
Regular Contributor

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

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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

TheoFaull
Occasional Contributor III

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
MVP Esteemed Contributor

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
Regular Contributor III

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
Occasional Contributor III

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...

0 Kudos