topic Re: SQL in "Extract by Attributes" Conditional Where Clause in Data Management Questions

SQL in "Extract by Attributes" Conditional Where Clause

CarolineSchneider — Mon, 21 Jul 2014 10:36:17 GMT

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!

Re: SQL in "Extract by Attributes" Conditional Where Clause

T__WayneWhitley — Mon, 21 Jul 2014 19:53:27 GMT

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

Re: SQL in "Extract by Attributes" Conditional Where Clause

CarolineSchneider — Mon, 21 Jul 2014 20:16:30 GMT

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

Re: SQL in "Extract by Attributes" Conditional Where Clause

T__WayneWhitley — Mon, 21 Jul 2014 20:31:27 GMT

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

Re: SQL in "Extract by Attributes" Conditional Where Clause

CarolineSchneider — Tue, 22 Jul 2014 09:19:55 GMT

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

Re: SQL in "Extract by Attributes" Conditional Where Clause

T__WayneWhitley — Tue, 22 Jul 2014 15:37:52 GMT

Hi Caroline,

You haven't answered the specifics of your error - are you still having the 'invalid SQL statement' error? Also, you didn't say what version of ArcGIS and db source you're using (or I missed it).

Anyway, for what it's worth, you have to be careful with your logic and your statement is getting pretty long, perhaps convoluted. Consider this, with my above SQL query example - similar to yours, I am combining clauses that all must eval to True to select the record, that is everything within the 'compound' clause and everything without as well. That means when you connect with 'AND' 1 clause you know works to another clause, both must evaluate to True to make the selection. See this, which for my dataset only selected a single record (to illustrate the concept):

(NOT "RNG" = 33 AND NOT "RNG" = 34 AND "TWP" = 62)

AND

(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

This is because not only does the 2nd clause (= 3) have to be satisfied, but so does the 1st. It must not contain those records where the range is 33 nor 34. Not only that but with '...AND "TWP" = 62', that restricts the entire results to the township coded 62 only. So if your SQL is not in error (the above query works at 10.2.1), then check your logic.

-Wayne

Re: SQL in "Extract by Attributes" Conditional Where Clause

CarolineSchneider — Wed, 23 Jul 2014 08:00:33 GMT

Hi Wayne,

I am using the 10.1 version of ArcGIS. The statment is still invalid.

I am quite sure that there is no Problem wih my logic. I have a data base of 27,000 rows and eight columns of interesst. Each field can only have one of the following numbers: 1,2,3,4

The db file Ends tif.vat. I am not sure if this is all the Information you need on the db.

the problem lies in the CASE WHEN- expression

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

CASE WHEN "1986" =1 THEN 1 ELSE 0 END) = 2

If I would type in this single Expression, should it work? ArcGIS notes: an invalid SQL statment was used.

Thanks again for your help,

Caroline

Re: SQL in "Extract by Attributes" Conditional Where Clause

DanPatterson_Retired — Wed, 23 Jul 2014 08:25:38 GMT

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?

Re: SQL in "Extract by Attributes" Conditional Where Clause

CarolineSchneider — Tue, 05 Aug 2014 09:32:30 GMT

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

Re: SQL in "Extract by Attributes" Conditional Where Clause

T__WayneWhitley — Sun, 12 Dec 2021 02:00:08 GMT

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