topic Re: Setting OID Fields in Make Query Layer Function in Python Questions

Setting OID Fields in Make Query Layer Function

MikeMacRae — Thu, 02 Jun 2016 23:31:19 GMT

Hey all, I am trying to use the:

MakeQueryLayer_management

funtion using arcpy in ArcGIS 10.3.

Make Query Layer—Data Management toolbox | ArcGIS for Desktop

I am trying to set 2 fields as the OID fields (parameter #4). For the life of me, I can't get them to set in the resulting Query Layer. It will set the first one field, but not the second one. I am running this from both a python addin and from the built in python command line window in ArcMap:

arcpy.MakeQueryLayer_management (r"Database Connections\XXXX.sde", "Table_Test3", "select a.tenure_number_id, b.event_number_id from mta_acquired_tenure_svw a, mta_event c, mta_tenure_event_xref b where a.tenure_number_id = b.tenure_number_id and b.event_number_id = c.event_number_id", "TENURE_NUMBER_ID (Long Integer, Not nullable);EVENT_NUMBER_ID (Long Integer, Not nullable)")

The fields are valid. If I were to create a Query layer via the GUI in ArcMap (i.e. File-->Add Data-->Add Query Layer...), the query layer successfully populates and both the OID fields are set because I can manually check off each layer in the second page of the dialogue box. I tried reversing the fields in the query (shot in the dark) and what I've noticed is, it will set the first field in the table as the OID, but not the second. I've tried using the table aliases in the field names. No luck. Any suggestions? thanks.

Re: Setting OID Fields in Make Query Layer Function

JoshuaBixby — Fri, 03 Jun 2016 13:32:09 GMT

This definitely has all the signs of a bug. I ran the Make Query Layer tool and then used "Copy as Python Snippet" from the Results window. The code given to me does not reproduce the results of running the tool from the GUI. I would open an Esri Support case to log a bug.

Re: Setting OID Fields in Make Query Layer Function

MikeMacRae — Fri, 03 Jun 2016 15:12:12 GMT

Thanks for taking the time to test Joshua. It seems buggy. I've played with a variety of settings and it just doesn't seem to read that parameter. I could literally put some nonsense into that parameter like so:

arcpy.MakeQueryLayer_management (r"Database Connections\XXXX.sde", "Table_Test3", "select a.tenure_number_id, b.event_number_id from mta_acquired_tenure_svw a, mta_tenure_event_xref b, mta_event c where a.tenure_number_id = b.tenure_number_id and b.event_number_id = c.event_number_id", "This is a buggy parameter")

and it will still run, but ignore that setting. Very frustrating.

Re: Setting OID Fields in Make Query Layer Function

DanPatterson_Retired — Fri, 03 Jun 2016 15:21:08 GMT

just curious, since there seems to be no standard amongst databases or programming languages, I just want to make sure the '=' means and equality check and not an assignment check? Some environments use the more useful '==' as a test for equality and '=' when assigning values

Re: Setting OID Fields in Make Query Layer Function

MikeMacRae — Fri, 03 Jun 2016 16:17:54 GMT

Hey Dan, thanks for chiming in. The third parameter in the arcpy.MakeQueryLayer_management function accepts a string representation of an sql statement which doesn't use '==' to show equality. I can literally build an sql statement in Oracle developer using sql language and then copy/paste into the third parameter and wrap it in quotes. This is why I really like the query layer function. I just wish it would work as designed!

Re: Setting OID Fields in Make Query Layer Function

DanPatterson_Retired — Fri, 03 Jun 2016 16:24:55 GMT

Ok... I never work with them, but the suggestion from the help

fields

[oid_fields,...]

(Optional)

shows a list of oid fields, and to me a list is in [ ] because I work with python, but I have seen situations where arctoolbox 'lists' return semi-colon delimited text strings (I have no clue why). So they give no multi-case example in the help topic so I can't confirm if this is the normal behaviour for this tool. So if you have gotten this to work in the past as you have shown with a different file, then all I can suggest is try to build the query starting with one, then 2 etc to see if/how it works since it may identify one situation where it doesn't.

so to summarize = is ok, string enclosure is suggested as ok, leaving is a comma-separated or semi-colon 'list' representation since I doubt a [ ] would be used outside of python

Re: Setting OID Fields in Make Query Layer Function

MikeMacRae — Fri, 03 Jun 2016 16:33:56 GMT

Thanks again Dan. Amoung the many approaches I have tried, they include your suggestions above. I have tried a python list []. that doesn't work. Same goes with a semicolon delimited string list as in the help example. That doesn't work. In all cases, when I run the command in python, whichever field is first in the resulting table, gets set as the OID. Every other field is ignored. So, for example, if I use 2 or more fields as OID's, only the first field in the table gets set as the OID. I've also tested by setting the OID to just the second field in the table. No luck. It still sets the OID to the first field in the table. Again, the function just seems to ignore that parameter altogether....

Re: Setting OID Fields in Make Query Layer Function

DanPatterson_Retired — Fri, 03 Jun 2016 16:45:10 GMT

Ok... you seem to have covered all the possibilities... I would suggest shipping this off to tech support for commentary, either there is an error, a documentation bug, or an 'issue' that isn't readily obvious.. Good luck and report back

Re: Setting OID Fields in Make Query Layer Function

RyanMonk1 — Fri, 10 Jun 2016 09:43:20 GMT

Any news on this?

BUG-000090452: The arcpy.MakeQueryLayer_management() command does n..

Found the same thing mentioned on stackexchange: arcgis 10.2 - Can't specify OID field in arcpy.MakeQueryLayer_management - Geographic Information Systems Stack Exchange

Re: Setting OID Fields in Make Query Layer Function

MikeMacRae — Fri, 10 Jun 2016 19:02:05 GMT

Hey Ryan. I spoke to ESRI this week about it. We went through some testing and they were able to replicate the issue. It was elevated to the Enterprise team for research and a fix. Unfortunately, if a fix is made, if won't be avalaible for the next service pack in 10.4. Thanks for finding the BUG number. I didn't find that, nor did ESRI when I chatted with them.

Re: Setting OID Fields in Make Query Layer Function

RyanMonk1 — Tue, 14 Jun 2016 03:47:08 GMT

Thank you for the update

This bug is currently causing me trouble in ArcGIS 10.2 and Oracle 11g, I'm trying to get a python script automate the exporting of records from dynamically generated query layers. I can't seem to get it to function exactly the same as the manual process of doing 'File / Add Data / Add Query Layer' then right click 'Export' .

For now I just add an Oracle ROWNUM as the first column in the query to generate an OID. To make it work I needed to cast it to a Number(10) otherwise ArcGIS thinks it is a Double and won't use it. "select cast(rownum as number(10)) as objectid, * from ..."

Re: Setting OID Fields in Make Query Layer Function

PhilLarkin1 — Thu, 14 Jul 2016 19:06:13 GMT

It appears a fix didn't make it in 10.4.1
http://downloads.esri.com/support/downloads/other_/10-4-1-IssuesAddressedList.pdf

Re: Setting OID Fields in Make Query Layer Function

MattWilkie3 — Wed, 14 Nov 2018 21:13:28 GMT

Thank you for the rownum work-around Ryan. I've added it to the conversation at arcgis desktop - Converting double field to integer in database view so ArcMap query layer can use field as unique ident…