I have a python script that is used as a toolbox that allows users to plug in a few variables and then a map is created for them, filtering the layers based on their input. It works fine in ArcMap 10.2
But for some reason it has errors in ArcMap 10.3
where it bombs is the MakeTableView tool. The error statement is saying I have an improper SQL statement.
What has changed from 10.2 to 10.3 that would make my SQL statement invalid?
Anyone know how to fix this?
the end user wants to use 10.3
I would recommend using AddFieldDelimiters—Help | ArcGIS for Desktop . See the sample code below:
import arcpy
lst_ws = [r'D:\Xander',
r'D:\Xander\GeoNet\ExtractFeatures\Indonesia.gdb',
r'D:\Xander\Apoyo\ConsumoCartera\mdb\test.mdb',
r'Database Connections\Desarrollo 10.1.sde']
for ws in lst_ws:
fld1 = arcpy.AddFieldDelimiters(ws, 'a')
fld2 = arcpy.AddFieldDelimiters(ws, 'c')
print "({0} = {1}) AND ({2} = '{3}')".format(fld1, 10, fld2, 'Y')
This will yield:
("a" = 10) AND ("c" = 'Y')
(a = 10) AND (c = 'Y')
( = 10) AND ( = 'Y')
(a = 10) AND (c = 'Y')
unfortunately that didn't seem to do it either. Same error message:
(DISPOSITION_TRANSACTION_SID = 923413) AND (PRIMARY_CONTACT_YRN = 'Y')
ERROR 000358: Invalid expression
Failed to execute (MakeTableView).
I altered my script to use the AddFieldDelimiter:
t1 = self.bcgw + '\\WHSE_TANTALIS.TA_TENANTS' # gobetween table
t2 = self.bcgw + '\\WHSE_TANTALIS.TA_INTERESTED_PARTIES'
# fields for joining the tables
t1Field = 'DISPOSITION_TRANSACTION_SID'
joinField = 'INTERESTED_PARTY_SID'
contField = 'PRIMARY_CONTACT_YRN'
# only pulling out client identified as "Primary Contact"
# editing out and trying AddFieldDelimiters
# http://desktop.arcgis.com/en/arcmap/latest/analyze/arcpy-functions/addfielddelimiters.htm
# whereClause = t1Field + " = " + str(dispID) + " AND \"PRIMARY_CONTACT_YRN\" = \'Y\'"
wcList = [t1Field, contField]
wc1 = arcpy.AddFieldDelimiters(self.bcgw, t1Field)
wc2 = arcpy.AddFieldDelimiters(self.bcgw, contField)
whereClause = "({0} = {1}) AND ({2} = '{3}')".format(wc1, str(dispID), wc2, 'Y')
arcpy.AddMessage(whereClause)
legal = 'LEGAL_NAME' # field names in the TA_INTERESTED_PARTIES table
first = 'FIRST_NAME'
last = 'LAST_NAME'
txtClientName = 'Interest Holder: ' # resetting txtClientName
arcpy.MakeTableView_management (t1, "t1View", whereClause)
arcpy.AddMessage("created t1View")
I assume you will have to implement: ParseFieldName—Help | ArcGIS for Desktop to get the fully qualified field names.
i'm not familiar with that and the help seems to make it look more like a way to connect to the database(?) Or it creates some long string to connect(?)
can you explain how this would be used in the context of my MakeTableView problem?
thank God a colleague of mine was able to help me out.
It was not a syntax thing but an ESRI Bug of some sort that's referenced in another thread:
https://community.esri.com/thread/170160#comment-581568
MakeTableView doesn't work properly in some situations using 10.3 even though the exact same script using the exact same dataset will work in 10.2
ESRI : please fix this ASAP! Copying out my table into another table to add ObjectID before making my virtual table seems kinda redundant and pointless...
If anyone from ESRI read these forums please add to your to-do list - please and thank you!
from that thread, it is painfully obvious that no one filed a bug report... I hope you do this time, If they get filed they get fixed and no one's time gets wasted
is there a bug submission page somewhere?
Via the usual route... tech support http://support.esri.com/contact-tech-support
log a case, you will need to provide data usually for testing
And the importance of objectid fields is discussed in the help topics which also raises the importance of reporting the types and sources of tabular data one works with. Information on how to create them is also given...