AnsweredAssumed Answered

Invalid SQL statement?

Question asked by Higham.chr@gmail.com on Nov 15, 2018
Latest reply on Nov 16, 2018 by jborgion

Like • Show 0 Likes0
Comment • 3

I have the following line of code in my python script, (spaces inserted between quotation marks for readability):

arcpy.Select_analysis(Points_UTM, "fiftyPercent", " " " 'NEAR_DIST' <= 'dist50' " " ")

It creates a new feature class named "fiftyPercent", which is what I intended. But it has all the same features as the original layer, while I want it to only select and transfer half of the features.

I suspect I made an error in the SQL statement, but I can't find any help as to why. According to the select_analysis help, the tool transfers all features if no SQL statement is used.

Is my suspicion correct? If so, how might I change the format or syntax of my SQL statement?

No one else has this question

Outcomes

GSCUser85

Nov 16, 2018 12:24 AM

There are many reasons for the sql statement to be wrong. Firstly where is Points_UTM living?

fgdb, shapefile, pgdb, enterprise db etc.

I have often found it useful to actually make the sql select by hand, in the feature definition for example

1 person found this helpful

Actions

Higham.chr@gmail.com @ GSCUser85 on Nov 16, 2018 12:41 AM

Points_UTM is a feature class in a file geodatabase. I would do this part manually, except that it is in the middle of a geoprocessing workflow which is run in a python script tool. The rest of the tool works correctly, but the results are incorrect because the select function is not selecting anything with the SQL statement. Instead it creates a new feature class with all the same features as Points_UTM.

What might I change about the third parameter in the argument to get a different result?

Actions

jborgion

Nov 16, 2018 4:03 AM

It seems odd to me that it works at all: typically any selection operation needs to made on a feature layer rather than the feature class itself. That said, I have struggled with where clauses as well. Neil Ayres advice of trying it manually first is a very good way to go. All those quotes get confusing; you might want to try a where clause using variables and the format() method. Something like this: (untested)

f1 = 'NEAR_DIST'
f2 = 'dist50'

where = '{} <= {}'.format(f1,f2)

I assume that both fields are some flavor of numeric type, right?

2 people found this helpful

Actions

3 Replies

GSCUser85 Nov 16, 2018 12:24 AM
Mark Correct
Correct Answer
There are many reasons for the sql statement to be wrong. Firstly where is Points_UTM living?
fgdb, shapefile, pgdb, enterprise db etc.
I have often found it useful to actually make the sql select by hand, in the feature definition for example
1 person found this helpful
Like • Show 0 Likes0
Actions
- Higham.chr@gmail.com @ GSCUser85 on Nov 16, 2018 12:41 AM
  Mark Correct
  Correct Answer
  Points_UTM is a feature class in a file geodatabase. I would do this part manually, except that it is in the middle of a geoprocessing workflow which is run in a python script tool. The rest of the tool works correctly, but the results are incorrect because the select function is not selecting anything with the SQL statement. Instead it creates a new feature class with all the same features as Points_UTM.
  
  What might I change about the third parameter in the argument to get a different result?
  Like • Show 0 Likes0
  Actions
jborgion Nov 16, 2018 4:03 AM
Mark Correct
Correct Answer
It seems odd to me that it works at all: typically any selection operation needs to made on a feature layer rather than the feature class itself. That said, I have struggled with where clauses as well. Neil Ayres advice of trying it manually first is a very good way to go. All those quotes get confusing; you might want to try a where clause using variables and the format() method. Something like this: (untested)
```
f1 = 'NEAR_DIST'
f2 = 'dist50'

where = '{} <= {}'.format(f1,f2)
```
I assume that both fields are some flavor of numeric type, right?
2 people found this helpful
Like • Show 0 Likes0
Actions

GeoNet

The Esri Community

Invalid SQL statement?

Outcomes

Related Content

Recommended Content