Setting OID Fields in Make Query Layer Function

[MikeMacRae]

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.

[JoshuaBixby]

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.

[MikeMacRae]

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.

[DanPatterson_Retired]

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

[MikeMacRae]

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!

[DanPatterson_Retired]

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

[MikeMacRae]

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

[DanPatterson_Retired]

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

[RyanMonk1]

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

[MikeMacRae]

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.