nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Can't get reference to result from MakeQueryLayer_management

TimHaverland4

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

Finde mehr Beiträge mit dem Tag

Accepted answers

TimHaverland4

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:

All comments

MattWilkie3

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!

DamonPettitt

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

DavidParsons1

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

MarkPeacey2

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.

DamonPettitt

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.

DamonPettitt

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

OttarViken_Valvåg

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.

TimHaverland4

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.

TimHaverland4

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.

ScottBlankenbeckler

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.

RussellBrennan

Tim,

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

TimHaverland4

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

ChristopherThompson

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.

RussellBrennan

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?

TimHaverland4

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

ChristopherThompson

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

Nimm an der Unterhaltung teil

Angemeldete Mitglieder können Beiträge verfassen, Updates folgen und mehr. Neu hier? Registriere ein kostenloses Konto.

Schnellzugriffe

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.