I am trying to apply an SQL-funktion within the "Extract by Attributes"-Tool of ArcGIS. I have a Table of 27.000 rows and 2+8 columns. I would like to select only those rows, where the event field=1 happens only 3 times in all. Values within those columns are 1, 2, 3 or 4. The tool already provides the select funktion part, there after: ( CASE WHEN "1984" = 1 THEN 1 ELSE 0 END AND CASE WHEN "1986" = 1 THEN 1 ELSE 0 END AND CASE WHEN "1987" = 1 THEN 1 ELSE 0 END AND CASE WHEN "1991" = 1 THEN 1 ELSE 0 END AND CASE WHEN "2000" = 1 THEN 1 ELSE 0 END AND CASE WHEN "2003" = 1 THEN 1 ELSE 0 END AND CASE WHEN "2006" = 1 THEN 1 ELSE 0 END AND CASE WHEN "2011" = 1 THEN ELSE 0 END ) >= 3 The SQL statment is not excepted by the ArcGIS-tool. So something must be wrong here. Greatefull for any ideas!

Hello Wayne, hello Dan, thank you for your support on this! I have tried to calculate the "dummy"-field in arcgis via python, but did not succeed. So instead i did the same thing in open office and then I reintroduced those values into the table by using a table join. It's not as elegant as your suggestion Wayne, but I got there anyway. So thanks again, Caro

You are correct (and so is Dan). Based on your results, the problem is with the CASE statement. I tested the same datasource (file gdb) at both 10.0 and 10.2.1 -- the 10.0 test failed with the file gdb source but passed with an sde source; no problems at all with 10.2.1. So what I suggest to help make this easier on yourself is (if you can) calculate a new 'dummy' field (integer type) just to hold this CASE result, conditionally calculating the 2nd compound clause, this part: (CASE WHEN "1984" =1 THEN 1 ELSE 0 END + CASE WHEN "1986" = 1 THEN 1 ELSE 0 END + CASE WHEN "1987" = 1 THEN 1 ELSE 0 END + CASE WHEN "1991" = 1 THEN 1 ELSE 0 END + CASE WHEN "2000" = 1 THEN 1 ELSE 0 END + CASE WHEN "2003" = 1 THEN 1 ELSE 0 END + CASE WHEN "2006" = 1 THEN 1 ELSE 0 END + CASE WHEN "2011" = 1 THEN 1 ELSE 0 END) You should be able to do that with, say, a simple Python code block in the field calculator, something simple like the below - there's a less tedious way to write this function in more condensed 'pythonic' form, but this way you can more readily see what it's doing - this is the code block: def subQry(yr84, yr86, yr87, yr91, yr00, yr03, yr06, yr11): counter = 0 if yr84 == 1: counter += 1 if yr86 == 1: counter += 1 if yr87 == 1: counter += 1 if yr91 == 1: counter += 1 if yr00 == 1: counter += 1 if yr03 == 1: counter += 1 if yr06 == 1: counter += 1 if yr11 == 1: counter += 1 return counter ...and of course in the field calculator you'd check on the Python parser, and enter this expression statement to pass your fields: subQry(!1984!, !1986!, !1987!, !1991!, !2000!, !2003!, !2006!, !2011!) Then, say your 'dummy' field is called 'yourNewField', then your new SQL query based on this calculation should be valid (oops, previously I forgot the double-quote delimiters on "yourNewField", now corrected): NOT "2003"=3 AND NOT "2006" =3 AND "2011"=3 AND "yourNewField" >= 3

There is some vague reference to subqueries not being supported mid-way down Subqueries Note: Coverages, shapefiles, and other nongeodatabase file-based data sources do not support subqueries. Can you simplify your query to one condition then see if you can build upon it?

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; } } }

SQL in "Extract by Attributes" Conditional Where Clause

CarolineSchneider

I am trying to apply an SQL-funktion within the "Extract by Attributes"-Tool of ArcGIS.

I have a Table of 27.000 rows and 2+8 columns. I would like to select only those rows, where the event field=1 happens only 3 times in all. Values within those columns are 1, 2, 3 or 4.

The tool already provides the select funktion part, there after:

(CASE WHEN "1984" =1 THEN 1 ELSE 0 END AND CASE WHEN "1986" = 1 THEN 1 ELSE 0 END AND CASE WHEN "1987" = 1 THEN 1 ELSE 0 END AND CASE WHEN "1991" =1THEN 1 ELSE 0 END AND CASE WHEN "2000" =1 THEN 1 ELSE 0 END AND CASE WHEN "2003" =1 THEN 1 ELSE 0 END AND CASE WHEN "2006" =1 THEN 1 ELSE 0 END AND CASE WHEN "2011" =1 THEN ELSE 0 END) >= 3

The SQL statment is not excepted by the ArcGIS-tool. So something must be wrong here.

Greatefull for any ideas!

Trouver plus de publications taguées avec

Accepted answers

All comments

CarolineSchneider

Hello Wayne,

thanks again!

Since I have never programmed anything bevor it is most likely that I have made a blunder somewhere.

The entire input is as follows:

SELECT*FROM LCall.tif.vat WHERE: (This is already provided by the "Select by Attributes tool") ((Yes, Within the Spatial Analyst / Select by Attributes in the Attribute Table Window)

( NOT "2003"=3 AND NOT "2006" =3 AND "2011"=3 )

AND

(CASE WHEN "1984" =1 THEN 1 ELSE 0 END +

CASE WHEN "1986" = 1 THEN 1 ELSE 0 END +

CASE WHEN "1987" = 1 THEN 1 ELSE 0 END +

CASE WHEN "1991" = 1 THEN 1 ELSE 0 END +

CASE WHEN "2000" = 1 THEN 1 ELSE 0 END +

CASE WHEN "2003" = 1 THEN 1 ELSE 0 END +

CASE WHEN "2006" = 1 THEN 1 ELSE 0 END +

CASE WHEN "2011" = 1 THEN 1 ELSE 0 END)

>= 3

The expression before the following CASE WHEN.... is working on ist own. Also it is only one of a total of 6 different expressions I have already programmed and that work as intended.

I hope this claryfies things. Again thanks allot!

My Tabel is as follows:

>>> where_clause = '(CASE WHEN "1984" =1 THEN 1 ELSE 0 END + CASE WHEN "1986" = 1 THEN 1 ELSE 0 END + CASE WHEN "1987" = 1 THEN 1 ELSE 0 END + CASE WHEN "1991" = 1 THEN 1 ELSE 0 END + CASE WHEN "2000" = 1 THEN 1 ELSE 0 END + CASE WHEN "2003" = 1 THEN 1 ELSE 0 END + CASE WHEN "2006" = 1 THEN 1 ELSE 0 END + CASE WHEN "2011" = 1 THEN 1 ELSE 0 END) >= 3'
>>> 
>>> print where_clause
(CASE WHEN "1984" =1 THEN 1 ELSE 0 END + CASE WHEN "1986" = 1 THEN 1 ELSE 0 END + CASE WHEN "1987" = 1 THEN 1 ELSE 0 END + CASE WHEN "1991" = 1 THEN 1 ELSE 0 END + CASE WHEN "2000" = 1 THEN 1 ELSE 0 END + CASE WHEN "2003" = 1 THEN 1 ELSE 0 END + CASE WHEN "2006" = 1 THEN 1 ELSE 0 END + CASE WHEN "2011" = 1 THEN 1 ELSE 0 END) >= 3
>>>

OID	VALUE	COUNT	1984	1986	1987	1991	2000	2003	2006	2011
0	1	58461228	3	3	3	3	3	3	3	3
1	2	181039	3	3	3	3	3	3	4	3
2	3	2692	3	3	3	1	3	3	1	3
3	4	686	4	3	3	1	3	3	3	3
4	5	377228	1	1	1	1	1	1	1	1
5	6	3109	1	3	3	1	1	1	1	1

T__WayneWhitley

Could be a simple mistake, can you post a picture or code block if that is how you're running it? Also, what version ArcGIS are you using and what DB platform are you running against? You are using the Spatial Analyst tool, correct? (although that should not matter - should accept SQL)

As an initial guess, I think maybe you're not passing the query to the tool as a string - for example, when I run in Python a similar kind of SQL, notice it is a string (here I'm entering the backslash line continuation character for better readability):

>>> qry = '(CASE WHEN "RNG" >= 32 AND "RNG" <= 35 THEN 1 ELSE 0 END + \
               CASE WHEN "TWP" >= 62 AND "TWP" <= 66 THEN 1 ELSE 0 END + \
               CASE WHEN "LOCATION" LIKE \'%VACANT%\' THEN 0 ELSE 1 END) = 3'
>>>
>>>
>>> fc = r'C:\Users\whitley-wayne\Desktop\data_backup05131551.gdb\parcels20140102'
>>> import arcpy
>>> arcpy.MakeFeatureLayer_management(fc, 'testLyr', qry)
<Result 'testLyr'>

>>> theCount = arcpy.GetCount_management('testLyr')
>>> theCount.getOutput(0)
u'9485'
>>>

Your query string is fairly long, but I see nothing wrong with passing it as a single-line string, for example:

>>> where_clause = '(CASE WHEN "1984" =1 THEN 1 ELSE 0 END + CASE WHEN "1986" = 1 THEN 1 ELSE 0 END + CASE WHEN "1987" = 1 THEN 1 ELSE 0 END + CASE WHEN "1991" = 1 THEN 1 ELSE 0 END + CASE WHEN "2000" = 1 THEN 1 ELSE 0 END + CASE WHEN "2003" = 1 THEN 1 ELSE 0 END + CASE WHEN "2006" = 1 THEN 1 ELSE 0 END + CASE WHEN "2011" = 1 THEN 1 ELSE 0 END) >= 3'
>>> 
>>> print where_clause
(CASE WHEN "1984" =1 THEN 1 ELSE 0 END + CASE WHEN "1986" = 1 THEN 1 ELSE 0 END + CASE WHEN "1987" = 1 THEN 1 ELSE 0 END + CASE WHEN "1991" = 1 THEN 1 ELSE 0 END + CASE WHEN "2000" = 1 THEN 1 ELSE 0 END + CASE WHEN "2003" = 1 THEN 1 ELSE 0 END + CASE WHEN "2006" = 1 THEN 1 ELSE 0 END + CASE WHEN "2011" = 1 THEN 1 ELSE 0 END) >= 3
>>>

CarolineSchneider

Hi Wayne,

thanks, your suggestions are absolutely plausable.

I have corrected those mistakes and tried to run the algorithm again, but ArcGIS still states that there is a wrong SQL statement somewhere.

Do you happen to have any further thoughts on this?

Thank you so much for helping out!

-Cheers Caro

T__WayneWhitley

Looks to me, if I understand correctly your logic, you are simply missing the plus operator, like so:

(CASE WHEN "1984" =1 THEN 1 ELSE 0 END +
CASE WHEN "1986" = 1 THEN 1 ELSE 0 END +
CASE WHEN "1987" = 1 THEN 1 ELSE 0 END +
CASE WHEN "1991" = 1 THEN 1 ELSE 0 END +
CASE WHEN "2000" = 1 THEN 1 ELSE 0 END +
CASE WHEN "2003" = 1 THEN 1 ELSE 0 END +
CASE WHEN "2006" = 1 THEN 1 ELSE 0 END +
CASE WHEN "2011" = 1 THEN 1 ELSE 0 END)
>= 3

All you need is for the entire query to return True or False...but the way you have it set up testing each field conditionally, each field tested for the numeric value 1 will individually test True or False -- if True, you are assigning a value of 1, so if you want to test whether at least 3 of them eval to 1 then you have to add their respective results together. Looks like you had a little mistake toward the end too with 'THEN ELSE' (no value assigned for THEN).

Oh, almost forgot, when you use the 'AND', you're not adding...that would check for something like TRUE AND TRUE AND FALSE which would then be FALSE for the whole thing (wouldn't make sense to compare to 3).

-Wayne

PS- I suppose another example could be useful - certainly not the only way to run such a query, but this one applies similar SQL logic to select parcels in a subarea (by Township and Range) that are not coded (partial string) VACANT in a LOCATION field - this is Python using a SQL query in the SelectLayerByAttribute tool:

>>> qry = '(CASE WHEN "RNG" >= 32 AND "RNG" <= 35 THEN 1 ELSE 0 END + CASE WHEN "TWP" >= 62 AND "TWP" <= 66 THEN 1 ELSE 0 END + CASE WHEN "LOCATION" LIKE \'%VACANT%\' THEN 0 ELSE 1 END) = 3'


>>> arcpy.SelectLayerByAttribute_management("parcels20140102", "NEW_SELECTION", qry)
<Result 'parcels20140102'>
>>>

Rejoindre la conversation

Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.

Liens rapides

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