topic Re: Need help with a difinition query in Data Management Questions

Need help with a definition query

TheoFaull — Wed, 22 Jun 2016 15:54:50 GMT

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

Re: Need help with a difinition query

DarrenWiens2 — Wed, 22 Jun 2016 16:06:10 GMT

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?

Re: Need help with a difinition query

MicahBabinski — Wed, 22 Jun 2016 16:27:42 GMT

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

Re: Need help with a difinition query

JoshuaBixby — Wed, 22 Jun 2016 16:49:49 GMT

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

Re: Need help with a difinition query

TheoFaull — Thu, 23 Jun 2016 08:17:03 GMT

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.

Re: Need help with a difinition query

TheoFaull — Thu, 23 Jun 2016 08:24:44 GMT

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!

Re: Need help with a definition query

DanPatterson_Retired — Thu, 23 Jun 2016 08:31:54 GMT

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

Re: Need help with a definition query

WesMiller — Thu, 23 Jun 2016 12:08:20 GMT

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

Re: Need help with a definition query

TheoFaull — Thu, 23 Jun 2016 13:16:03 GMT

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

Re: Need help with a definition query

WesMiller — Thu, 23 Jun 2016 13:30:18 GMT

Due to an alpha numeric sort versus a numeric sort. alphanumeric = 1 10 11 12 13 14 15 16 17 18 19 2 20 21 ..., numeric sort = 1 2 3 4 5 6 7 8 9 10 ...

Re: Need help with a definition query

DarrenWiens2 — Thu, 23 Jun 2016 16:39:20 GMT

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.

Re: Need help with a definition query

TheoFaull — Wed, 29 Jun 2016 14:25:06 GMT

Another definition query question. My point shapefile contains a string field ("Tag") populated with values:

'AdHoc'

'AdHoc[followed by ref numbers]'

'Adhoc'

'Adhoc[followed by ref numbers]'

'TreeSurv'

etc. etc.

I wanted to split the layer so one only displayed points where the "Tag" field contains only AdHoc/Adhoc values.

"Tag" LIKE 'AdHoc%' found all these values but it missed out 'Adhoc'. Ok so it's case sensitive this query business. So...

"Tag" LIKE 'AdHoc%' AND "TAG" LIKE 'Adhoc%' BUT this returned no results.

What am I doing wrong here?

Re: Need help with a definition query

WesMiller — Wed, 29 Jun 2016 14:33:29 GMT

The use of AND requires both to be true for it to return anything. You could try OR or "Tag" LIKE 'Ad_oc%'

Re: Need help with a definition query

TheoFaull — Wed, 29 Jun 2016 14:33:45 GMT

Ok so it turns out replacing AND with OR fixes this. However this is the confusing bit...

I have another layer with a definition query which excludes certain values being shown, but uses the AND function. and it works!

"OWNERNAME" <> 'S_HOUSING' AND "OWNERNAME" <> 'R_LAND' AND "OWNERNAME" <> 'HOUSING ASSOCIATION'

This query successfully filters our any entries where the OWNERNAME field contains S_HOUSING, or R_LAND or HOUSING ASSOCIATION.

The OWNERNAME field is a string too FYI. How come AND works in this instance but not in the previous?

Re: Need help with a definition query

TheoFaull — Wed, 29 Jun 2016 14:34:53 GMT

excellent help again Wes Miller . see my post below though... hmmm

Re: Need help with a definition query

WesMiller — Wed, 29 Jun 2016 15:08:22 GMT

In this case <> means does not equal. So if "OWNERNAME" does not equal 'S_HOUSING' AND "OWNERNAME" does not equal 'HOUSING ASSOCIATION' when both queries evaluate to true then select it.