Python select_analysis where clause for numeric field

[PitersonPaulgek]

Hi,

I get error "Invalid expression" for:

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

What is the correct syntax?

[DarrenWiens2]

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]

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?

[DarrenWiens2]

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]

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

[DanPatterson_Retired]

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]

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!

[DanPatterson_Retired · Accepted solution]

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.

[MitchHolley1]

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

[PitersonPaulgek]

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!