Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I can't figure out the syntax to select table records where one field's value exceeds that of another. The fields are from a pair of table views connected w/AddJoin(). One view is of a fgdb table and the other is of a point feature class in a separate fgdb. My ArcGIS10.0 syntax looks like:

arcpy.MakeTableView_management(r'C:\Workspace\wk_NHDPlus\NHDPlus\NHDPlus_national.gdb\POIs\GagesII', 'gagesTV', '"REGION" = \'02\' AND "COMID" = 0')
arcpy.AddJoin_management('msrTV', 'ComID_Measures_REACHCODE', 'gagesTV', 'REACHCODE', 'KEEP_ALL')   
arcpy.SelectLayerByAttribute_management('msrTV', 'NEW_SELECTION', '"GagesII.MEASURE" >= "ComID_Measures02.ComID_Measures_BOTMEAS" ')

I've also tried:

newName = arcpy.AddFieldDelimiters(r'C:\Workspace\wk_NHDPlus\NHDPlus\NHDPlus_national.gdb\ComID_Measures02', "ComID_Measures02.ComID_Measures_BOTMEAS")
arcpy.SelectLayerByAttribute_management('msrTV', 'NEW_SELECTION', '"GagesII.MEASURE" > '+newName)

The result in both cases is the entirety of the original 'msrTV', which I know is incorrect.

Is what I'm trying to do possible? Hopefully I'm just fouling up the syntax.

* update: also tried:

newName = arcpy.AddFieldDelimiters(r'C:\Workspace\wk_NHDPlus\NHDPlus\NHDPlus_national.gdb\ComID_Measures02', "ComID_Measures02.ComID_Measures_BOTMEAS")
oldName = arcpy.AddFieldDelimiters(r'C:\Workspace\wk_NHDPlus\NHDPlus\NHDPlus_national.gdb\ComID_Measures02', "GagesII.MEASURE")
sqlquery=oldName+'>='+newName
arcpy.SelectLayerByAttribute_management('msrTV', 'NEW_SELECTION', sqlquery)
share|improve this question
    
Could you add in the part of your code where you are setting up the join and assigning it to the variable that you pass to the Attribute selection function? It seems like you are on the right track, but having some context might help. –  Get Spatial Apr 26 '13 at 16:52
    
@"Get Spatial". Sure. Added a couple of lines. Thanks for looking. Happy to add more if that would be helpful. Just didn't want to clog the question w/more than you would want. I can attach a field listing for the joined table, too. I'm not sure I'm understanding your question about the "the variable that you pass to the Attribute selection function". I'm trying to compare two fields. Let me know what I need to explain better. –  Roland Apr 26 '13 at 16:57
    
Started running this w/in ArcMap, using interactive tools. Noticing that the syntax "FIELD1" >= "FIELD2" verifies when referring to local fields and there is no AddJoin() in effect. When an AddJoin() is in place, the quotation marks disappear from the SelectByLayberByAttribute menu...not sure why. When I build the query on the joined table, neither quoted or unquoted field names are acceptable when I press the Verify button. –  Roland Apr 26 '13 at 18:19
    
Roland, this method of running the interactive tool and looking at the python snippets is a surefire way to deal with syntax problems in your expressions. This is sure weird. I don't know if it is the long field names either.. very strange. In general I try to keep field names short (<=10) and dbf-friendly (when I can) in case I need to go through that format in my processing workflow. –  Curtis Price Apr 26 '13 at 21:13

1 Answer 1

up vote 1 down vote accepted

Well, I figured out the syntax that seems to work. Don't really see why this should work better than what I had or at all, but here it is:

When using SelectLayberByAttribute on a pair of AddJoin'd tables, I needed to refer to the fields in the local table with an unquoted table.field syntax and the fields in the foreign table with a quote table.field syntax.

So to use the example from my question, where the local and foreign tables were ComID_Measures and GagesII, respectively, the syntax that worked was:

arcpy.SelectLayerByAttribute_management("ComID_Measures", 'NEW_SELECTION', 'ComID_Measures.ComID_Measures_BotMeas <= "GagesII.MEASURE" AND "GagesII.MEASURE" <= ComID_Measures.ComID_Measures_TopMeas')

I discovered this by messing with the interactive SelectLayerByAttribute tool using other joined tables. With these other data, I noticed the difference in quotation. Oddly, the data in my example didn't show this difference in the same tool. Maybe because field names were longer? Still a bit of a mystery to me. This wasn't clear from any doc I found.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.