nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Find more posts tagged with

Accepted answers

All comments

WesMiller

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.

TheoFaull

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

TheoFaull

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?

WesMiller

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

TheoFaull

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?

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.

WesMiller

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

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

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

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

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!

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.

JoshuaBixby

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

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

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?

GrantSmith122_0-1767043856140.png

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.