topic Re: ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why? in Python Questions

ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why?

JoshuaChan — Wed, 24 Aug 2016 22:59:07 GMT

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

Re: ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why?

DanPatterson_Retired — Thu, 25 Aug 2016 00:38:22 GMT

I suspect that seeing the sql statement would help. You can also look through the change list and bug fixed list here, part way down the link

/blogs/dan_patterson/2016/05/09/the-links

Re: ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why?

JoshuaChan — Thu, 25 Aug 2016 18:20:20 GMT

good point...

here's the print out of my SQL statement plus the error message:

DISPOSITION_TRANSACTION_SID = 923413 AND PRIMARY_CONTACT_YRN = 'Y'
ERROR 000358: Invalid expression
Failed to execute (MakeTableView).

DISPOSITION_TRANSACTION_SID and PRIMARY_CONTACT_YRN are both fields in tables

and the script runs fine when I run it in 10.2 but bombs in 10.3

Does it matter what version of ArcGIS the mxd was created in? the original one was probably 10.1.

Re: ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why?

DanPatterson_Retired — Thu, 25 Aug 2016 18:26:56 GMT

the sql stuff is dependent on where the data resides... some queries like double-quotes around the field names, some square brackets, some doesn't matter... I think they all agree on single-quotes around strings as in 'Y'.

One thing that is not clear amongst the flavors is whether things separate by 'and' requires round brackets or not or whether and is AND or and (ie case). So off you go to SQL reference for query expressions used in ArcGIS—Help | ArcGIS for Desktop and track down the source where the data resides to check the appropriate conditions for your data source. You didn't indicate whether everything was the same between versions or not and I guess nothing popped up in the version change history.

Re: ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why?

JoshuaChan — Thu, 25 Aug 2016 22:33:54 GMT

the data is sitting in ArcSDE and is the same data. It's only the ArcGIS version that's different (10.2 vs 10.3)

in the past the SQL hasn't been an issue. I'll see if fiddling with double-single quotes helps or round/square/curly brackets helps or not.

when I remove that reference to that module that script works fine so it is something about the MakeTableView_Management tool that has changed or as you suggest, maybe something about how SQL needs to be formatted in 10.2 vs 10.3?

Re: ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why?

JoshuaChan — Thu, 25 Aug 2016 22:56:29 GMT

unfortunately the double quotes around the field names didn't help. I also tried the square brackets.

I was looking earlier in my script and I have a SQL statement (using the same format) that I was using in a definition query on a layer (also stored in SDE) and that worked fine in both 10.2 and 10.3

I was about to copy/paste my module (after adding double quotes) here but it looks like all formatting disappears (spaces, tabs etc). So here it is on an FTP site:

ftp://ftp.geobc.gov.bc.ca/pub/outgoing/_SouthCoast_Region/clientNameModule.txt

Re: ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why?

DanPatterson_Retired — Sat, 11 Dec 2021 16:50:48 GMT

I rarely use queries, but when I do, I find the python minilanguage useful for getting rid of those pesky pluses and backslashes and weird stuff. try replacing the "" with[ ] and the like, I do like ( ) around sub queries and I would still check on the 'and' thing

>>> test = '("{}" = {}) AND ("{}" = \'Y\')'.format('a', 10, 'c')
>>> print(test)
("a" = 10) AND ("c" = 'Y')
‍‍‍

Re: ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why?

JoshuaBixby — Fri, 26 Aug 2016 01:54:53 GMT

What is the backend DBMS?

Re: ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why?

JoshuaChan — Fri, 26 Aug 2016 16:59:38 GMT

Oracle. Not sure what version. I can find out if the version of Oracle matters.

but that part remains the same... when I run in 10.2 or 10.3 it's still the same DBMS.

Re: ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why?

JoshuaBixby — Fri, 26 Aug 2016 17:59:59 GMT

I don't think the Oracle version will matter, but knowing it is Oracle may help troubleshoot. Thanks.

Re: ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why?

XanderBakker — Sat, 11 Dec 2021 16:50:51 GMT

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

Re: ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why?

JoshuaChan — Mon, 29 Aug 2016 16:30:55 GMT

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

Re: ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why?

XanderBakker — Mon, 29 Aug 2016 16:44:45 GMT

I assume you will have to implement: ParseFieldName—Help | ArcGIS for Desktop to get the fully qualified field names.

Re: ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why?

JoshuaChan — Mon, 29 Aug 2016 17:14:12 GMT

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?

Re: ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why?

JoshuaChan — Tue, 30 Aug 2016 18:42:13 GMT

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!

Re: ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why?

DanPatterson_Retired — Tue, 30 Aug 2016 19:01:10 GMT

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

Re: ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why?

JoshuaChan — Tue, 30 Aug 2016 19:49:33 GMT

is there a bug submission page somewhere?

Re: ArcPy: MakeTable_View works in 10.2 but not in 10.3? Why?

DanPatterson_Retired — Tue, 30 Aug 2016 20:19:41 GMT

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

Fundamentals of ObjectID fields—Help | ArcGIS for Desktop