topic Re: Can't get reference to result from MakeQueryLayer_management in Python Questions

Can't get reference to result from MakeQueryLayer_management

TimHaverland4 — Wed, 10 Oct 2012 19:13:43 GMT

When I try to run arcpy.MakeQueryLayer_management from the python command window, I can't get the resulting table added to the ArcMap display.

For example (I've changed the details to a generic case):

>>> arcpy.MakeQueryLayer_management('Database Connections\my_database.sde','a_table_view','select a_field from some_table','a_field')

The database I'm querying is Oracle 11g.

The command runs successfully (python returns <Result 'a_table_view'>, but when I go to the arctoolbox results tab, rt-click on the output, and choose Add to display I don't get anything. Likewise I can't seem to find this table through the various listing methods:

>>> mxd = arcpy.mapping.MapDocument("CURRENT") >>> df = mxd.activeDataFrame >>> arcpy.mapping.ListLayers(mxd,data_frame=df) [] >>> arcpy.mapping.ListTableViews(mxd,data_frame=df) [] >>>

I've also tried getting a reference from:

arcpy.mapping.TableView('a_table_view')

and get an Assertion error.

Any ideas?

Thanks,

Tim

Re: Can't get reference to result from MakeQueryLayer_management

ChristopherThompson — Wed, 10 Oct 2012 19:44:28 GMT

in this line 'Database Connections\my_database.sde' you're referencing an SDE connection or a workspace.. not a table.. the arguments for the MakeQueryTable command indicate that first argument should be a reference to a table so maybe something like:
'Database Connections\my_database.sde\table_name' would work instead?

MakeQueryTable_management (in_table, out_table, in_key_field_option, {in_key_field}, {in_field}, {where_clause})

Re: Can't get reference to result from MakeQueryLayer_management

TimHaverland4 — Thu, 11 Oct 2012 13:33:23 GMT

in this line 'Database Connections\my_database.sde' you're referencing an SDE connection or a workspace.. not a table.. the arguments for the MakeQueryTable command indicate that first argument should be a reference to a table so maybe something like:
'Database Connections\my_database.sde\table_name' would work instead?

MakeQueryTable_management ( in_table, out_table, in_key_field_option, {in_key_field}, {in_field}, {where_clause})

Thanks for the reply. I'm using MakeQueryLayer_management, not MakeQueryTable_management. The first parameter for that tool is "input_database."

Do you have any experience with MakeQueryLayer_management?

Tim

Re: Can't get reference to result from MakeQueryLayer_management

RussellBrennan — Thu, 11 Oct 2012 15:25:16 GMT

Tim,

If you go into your geoprocessing options and check the box for 'Add results of geoprocessing operations to the display' do the results get added to your map?

Re: Can't get reference to result from MakeQueryLayer_management

ChristopherThompson — Thu, 11 Oct 2012 15:39:02 GMT

Thanks for the reply. I'm using MakeQuery Layer_managemen

Crap.. my bad.. I shouldn't respond to these things near the end of the day when my brain is jelly.

Re: Can't get reference to result from MakeQueryLayer_management

TimHaverland4 — Fri, 12 Oct 2012 13:06:41 GMT

Tim,

If you go into your geoprocessing options and check the box for 'Add results of geoprocessing operations to the display' do the results get added to your map?

Yes, that option is checked.

I've tried MakeQueryLayer_management using a query that returns a spatial column, and the result *does* get added to the display. So the problem seems to lie with queries that don't return a spatial column, resulting in a table (Table View?)

Update: also tried this with a postgres database with same result
Update 2: using File>Add Data>Add Query Layer... does add the result to ArcMap, as does running the Make Query Layer tool from model builder.
Tim

Re: Can't get reference to result from MakeQueryLayer_management

RussellBrennan — Fri, 12 Oct 2012 14:54:02 GMT

Tim,

I cannot reproduce this behavior on my machine, what version of ArcGIS (including service packs) are you using?

Re: Can't get reference to result from MakeQueryLayer_management

ScottBlankenbeckler — Sat, 11 Dec 2021 17:14:36 GMT

Tim,
Why are you not using the MakeQueryTable_management?

LOCATIONS_T = "Database Connections\\SDE DATABASE.sde\\USERNAME.LOCATION_T"
arcpy.MakeQueryTable_management(LOCATION_T, "QueryTable", "USE_KEY_FIELDS", "", "", "Job_Key = " + str(Job_Key))

This brings the queried table in to my project.

Re: Can't get reference to result from MakeQueryLayer_management

TimHaverland4 — Fri, 19 Oct 2012 11:19:56 GMT

Tim,

I cannot reproduce this behavior on my machine, what version of ArcGIS (including service packs) are you using?

I'm running 10.1 ... no service packs are available as far as I know.

I have submitted a tech support request on this (incident 1081341). The analyst on that incident can't reproduce either.

I've tried this on both Oracle 11g and PostGIS 9.2 with the same results (table not added and no way to get a reference). I've also tried on a Windows 7 computer w/ connection to same Oracle database with same problem.

Re: Can't get reference to result from MakeQueryLayer_management

TimHaverland4 — Sat, 11 Dec 2021 17:14:39 GMT

Tim,
Why are you not using the MakeQueryTable_management?
LOCATIONS_T = "Database Connections\\SDE DATABASE.sde\\USERNAME.LOCATION_T"
arcpy.MakeQueryTable_management(LOCATION_T, "QueryTable", "USE_KEY_FIELDS", "", "", "Job_Key = " + str(Job_Key))
This brings the queried table in to my project.

Hi Scott,

I really want to use the MakeQueryLayer because it allows any SQL statement to be issued (including complex joins, grouping, stored function calls, etc). Right now I'm just trying a straight select statement to see if I can get this working.

Re: Can't get reference to result from MakeQueryLayer_management

TimHaverland4 — Wed, 24 Oct 2012 15:54:29 GMT

I FIGURED OUT WHY IT'S NOT WORKING!!!!

When running MakeQueryLayer_management from the python command window and your query only returns non-spatial columns, you must turn off the "Enable Background Processing" option:

1) From the Geoprocessing menu, choose Geoprocessing Options...
2) Under the "Background Processing" section, uncheck "Enable"

I'd consider this a bug ... if you have background processing enabled, and you run MakeQueryLayer_management from the python command window using a query that returns a spatial column, a layer is added to your map document. But, if your query does not return a spatial column, the resulting table does not get added to your map document and there's no way to add it through python or via the results window.

Thanks for everyone who helped me get to this point!:cool:

Re: Can't get reference to result from MakeQueryLayer_management

OttarViken_Valvåg — Fri, 01 Feb 2013 11:32:36 GMT

This problems seems to exist in 10.1 SP1 as well. I'm unable to work with Query Layers against sql server data when the query returns non-spatial data, if I'm using python scripts running outside ArcMap. My workaround is to add a dummy sql server geometry column in my sql server data so that my query layer becomes a feature layer.

Re: Can't get reference to result from MakeQueryLayer_management

DamonPettitt — Sun, 26 Oct 2014 17:58:15 GMT

I was having the same problem with not being able to get the resulting table from MakeQueryLayer when not using a spatial column. I'm trying to work with this in a standalone python script so I'm not trying to add a table to ArcMap. I just want to copy out the table as a standalone table in some file geodatabase i'm working with.

I stumbled upon another forum (Table analog of MakeQueryLayer) that made me realize

I still think there's a bug as the help for MakeQueryLayer in 10.2.2 says "If the SQL query does not return a spatial column, the output will be a stand-alone table." which isn't true as it appears to return a pointer to a database (maybe similar to a sql view?). I'll contact ESRI about this.

Here's how i got it working in my python code:

arcpy.MakeQueryLayer_management(database_connection,"out_layer_name", query,"FIELD1;FIELD2")

desc = arcpy.Describe("out_layer_name")

t = arcpy.mapping.TableView(desc.name)

arcpy.CopyRows_management(t, "myoutputtable")

Re: Can't get reference to result from MakeQueryLayer_management

DamonPettitt — Tue, 28 Oct 2014 16:41:47 GMT

This is a know bug and the bug # is NIM086004.

The workaround i listed where you could "describe" the layer and turn ultimately into a table seems like the best workaround in the meantime.

Re: Can't get reference to result from MakeQueryLayer_management

MarkPeacey2 — Wed, 14 Oct 2015 05:52:28 GMT

It's great that a work around has been found for Python but Esri should really look at fixing this, especially as it's still an issue in 10.3.1. In Model Builder (thankfully we're not using it!) there's no way to use arcpy.Describe or mapping.TableView unless you add a python script to the model. There's another thread about this problem Create Query Layer Tool Output Object Not Found or Recognized which came to much the same conclusion.

Re: Can't get reference to result from MakeQueryLayer_management

DavidParsons1 — Fri, 18 Nov 2016 22:02:34 GMT

Folks is this bug back in 10.4.1 ? I appear to be getting it again .. Maybe ESRI never fixed it

Re: Can't get reference to result from MakeQueryLayer_management

DamonPettitt — Fri, 18 Nov 2016 23:51:09 GMT

I just checked the status of the bug # previously referenced and the status is "In Product Plan", so i guess that means it hasn't been addressed yet. :<(

Re: Can't get reference to result from MakeQueryLayer_management

MattWilkie3 — Wed, 12 Dec 2018 17:52:45 GMT

Still present in 10.6, though it's fixed in ArcPro. I've summarized your and Tim Haverland‌ diagnosis and solution at arcmap - Make Query Layer - where are the results? - Geographic Information Systems Stack Exchange . Thank you for getting me on the right track!