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

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

[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

[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?

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

[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

[RussellBrennan]

Tim,

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

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

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

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