Python select_analysis where clause for numeric field

2414
10
Jump to solution
10-18-2016 03:52 PM
PitersonPaulgek
New Contributor III

Hi,

I get error "Invalid expression" for:

where_clause ='"Yr" IS NULL'
arcpy.Select_analysis(InFC, OutFC, where_clause)

What is the correct syntax?

Tags (1)
0 Kudos
1 Solution

Accepted Solutions
DanPatterson_Retired
MVP Emeritus

Piterson, from what is given, then it appears field delimiters are not needed, which seems to be consistent with the suggestions in ArcGIS Pro ... now I am assuming that you hide your layer and field names to preserve anonymity given the syntax .... Select_analysis (in_features, out_feature_class, {where_clause}) ...

since ***, and *** are not obviously going to work in a python script.  You could try a simple script to test. by replacing the in_features and out_feature_class with the path to a file (shapefile or featurelayer in a gdb) for source and destination.

View solution in original post

0 Kudos
10 Replies
DarrenWiens2
MVP Honored Contributor

Try 'Yr IS NULL'. Otherwise, verify your expression in the tool GUI, and consult: Building a query expression—Help | ArcGIS for Desktop 

edit: it's also good practice to use AddFieldDelimiters to ensure correct field delimiters (brackets, quotes, or none).

edit 2: once you get something to work through the GUI, you can right-click in the results window and copy as Python snippet to see the syntax. It looks like ArcGIS will accept this syntax: where_clause=""""YR" IS NULL""" (four double-quotes at the beginning, three at the end)

PitersonPaulgek
New Contributor III

Thanks Darren,

I tried: the same error: Invalid expression.

I definitely got working with ModelBuilder first, then exported to Python.

So,

In GUI (ModelBuilder) (with SQL Query Builder wizard):

  Yr IS NOT NULL (it works in ModelBuilder without any quotes)

in Python (*.py):

I tried:

1) where_clause ='Yr IS NULL' - ERROR

2) where_clause ='"Yr" IS NULL' - ERROR

3) where_clause =""""Yr" IS NULL""" - ERROR
arcpy.Select_analysis(InFC, OutFC, where_clause)

For another field (text) it works fine in Python

arcpy.Select_analysis(InFC, OutFC, "Prime = 'Iron Ore'")

I've noticed that I work with layer(not feature class). I do MakeFeatureLayer that is the input for Select. Could it have an impact?

0 Kudos
DarrenWiens2
MVP Honored Contributor

What is the datasource for the feature layer (FGDB feature class, PGDB feature class, shapefile, other) and what is the datatype of the field YR?

PitersonPaulgek
New Contributor III

Datasource for the feature layer - FGDB feature class.

Datatype of the field YR - Long Integer

(Datatype of the field Prime - Text (it work fine - see comments above))

0 Kudos
DanPatterson_Retired
MVP Emeritus

You got it to work in modelbuilder as you indicate.  Could you export the model to a python script and report the syntax from the script? Don't run the script, just produce the format.  Perhaps there is a difference in the translation from model to script that doesn't work, or at least you will have the correct syntax to use from a standalone script.

PitersonPaulgek
New Contributor III

Thanks Dan,

I did the export. The syntax from the exported: 

# Process: Select (2)
arcpy.Select_analysis(***, ***, "Yr IS NOT NULL")

So, what is the correct? Please help!

0 Kudos
DanPatterson_Retired
MVP Emeritus

Piterson, from what is given, then it appears field delimiters are not needed, which seems to be consistent with the suggestions in ArcGIS Pro ... now I am assuming that you hide your layer and field names to preserve anonymity given the syntax .... Select_analysis (in_features, out_feature_class, {where_clause}) ...

since ***, and *** are not obviously going to work in a python script.  You could try a simple script to test. by replacing the in_features and out_feature_class with the path to a file (shapefile or featurelayer in a gdb) for source and destination.

0 Kudos
MitchHolley1
MVP Regular Contributor

Do a Select By Attributes on the table in ArcMap, then copy that SQL syntax to your Python code. 

PitersonPaulgek
New Contributor III

Thanks Mitch,

I did - it works fine in ArcMap Select By Attributes (SQL wizard). The syntax is: Yr IS NOT NULL

No quotes!

Any ideas, please!

0 Kudos