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

MakeQueryTable_Management

RameshGautam

I want to create a table called "Test" from inputTable called "Avg_Daily" using arcpy.MakeQueryTable_Management option of arcpy. However, I am getting error which says "Error 999999: An Invalid SQL statement was used". I don't know where I used invalid SQL statement. Any idea? Here is the code:

import arcpy from arcpy import env  env.workspace ="C:/RG/Projects/CriticalDataNetwork/CDEC.gdb"  try:     inTable = "Avg_Daily"     keyField = "STATION_ID"     inField = [["Avg_Daily.OBJECTID","OBJECTID",],["Avg_Daily.STATION_ID","STATION_ID"], ["Avg_Daily.DLY_AVG", "DLY_AVG"]]     whereClause = "\"STATION_ID\" = 'CAP', 'DDM'"     outTable = "Test"     arcpy.MakeQueryTable_management(inTable,  outTable, "USE_KEY_FIELDS", keyField, inField, whereClause)  except Exception, e:     # If an error occurred, print line number and error message     import traceback, sys     tb = sys.exc_info()[2]     print "Line %i" % tb.tb_lineno     print e.message

Encuentra más publicaciones etiquetadas con

Accepted answers

WilliamCraft

Now, the code runs without any problem, however, I can't see the output file. Any clue?

Your output "Test" table is merely a query table which is stored in memory and not something that is physically written somewhere. If you want to write your "Test" table out to disk, use the Table To Geodatabase (Conversion) tool and convert the table from being in memory to your workspace:

arcpy.TableToGeodatabase_conversion(outTable, "C:/RG/Projects/CriticalDataNetwork/CDEC.gdb")

All comments

RichardFairhurst

What is that where clause doing? It seems unlikely you have a field with a user entered string value that looks anything like what you typed. I would think that these are two fields being matched up, in which case the second half of the expression would not be quoted.

Using fully qualified field names can be tricky. Sometimes you don't need to use them. I always test broken expressions in ModelBuilder or Desktop query builder and paste that into Python scripts once the bugs are out. I never freehand type a complex field name or expression for fear of some nearly invisible typo sneaking in. Too much time can be wasted hunting for a misplaced period.

RameshGautam

Why am I getting an Invalid sQL Statement error in this code? where I, simply would like to pull the data from OLE DB connection.

import arcpy
from arcpy import env

try:
    inTable = ["Database Connections/OLE DB Connection.odc/ORADBA.COUNTY"]
    inField = [["ORADBA.COUNTY.OBJECTID", 'OBJECTID'], ["ORADBA.COUNTY.COUNTY_NUM", 'COUNTY_NUM'],\
              ["ORADBA.COUNTY.COUNTY_NAME", 'COUNTY_NAME']]
    keyField = "ORADBA.COUNTY.OBJECTID"
    whereClause = "\"ORADBA.COUNTY.COUNTY_NAME\" = 'ORADBA.COUNTY.KINGS'"

    outTable = "C:/RG/Projects/CriticalDataNetwork/CDEC/Kings"
    arcpy.MakeQueryTable_management(inTable, outTable, "USE_KEY_FIELDS", keyField, inField, whereClause)
    arcpy.TableToGeodatabase_conversion(outTable, "C:/RG/Projects/CriticalDataNetwork/CDEC.gdb")
except Exception, e:
    # If an error occurred, print line number and error message
    import traceback, sys
    tb = sys.exc_info()[2]
    print "Line %i" % tb.tb_lineno
    print e.message

RameshGautam

Thank you so much for the feedback. It is great to know the secrets behind the query table tool.
Thanks

Really, the MakeQueryTable tool is geared around doing complex queries with multiple related tables.

If you just want to extract some records to a new on-disk table use the Select_analysis tool.

If you want to make a in-memory pointer to some particular records in a table (aka 'a view') use the MakeTableView tool.

For example:
arcpy.Select_analysis(myInputTblPath, myOutputTblPath, "STATION_ID in ('CAP','DDM')")
#or
arcpy.MakeTableView_management(myInputTblPath, "my_table_view", "STATION_ID in ('CAP','DDM')")
BTW: Per my undersatnding, a QueryTable and TableView (and a FeatureLayer as well) do not store the actual records of the table in memory (aka the RAM). Rather they store references to the on-disk records. Basically it builds a hash table of the key fields that satisfy a particular SQL query. Only the keys and their relationships are stored in RAM.

ChrisSnyder

Really, the MakeQueryTable tool is geared around doing complex queries with multiple related tables.

If you just want to extract some records to a new on-disk table use the Select_analysis tool.

If you want to make a in-memory pointer to some particular records in a table (aka 'a view') use the MakeTableView tool.

For example:

arcpy.Select_analysis(myInputTblPath, myOutputTblPath, "STATION_ID in ('CAP','DDM')")
#or
arcpy.MakeTableView_management(myInputTblPath, "my_table_view", "STATION_ID in ('CAP','DDM')")

BTW: Per my undersatnding, a QueryTable and TableView (and a FeatureLayer as well) do not store the actual records of the table in memory (aka the RAM). Rather they store references to the on-disk records. Basically it builds a hash table of the key fields that satisfy a particular SQL query. Only the keys and their relationships are stored in RAM.

WilliamCraft

Now, the code runs without any problem, however, I can't see the output file. Any clue?

arcpy.TableToGeodatabase_conversion(outTable, "C:/RG/Projects/CriticalDataNetwork/CDEC.gdb")

RameshGautam

Or try:

whereClause = "\"STATION_ID\" IN ('CAP', 'DDM')"

Now, the code runs without any problem, however, I can't see the output file. Any clue?

RichardFairhurst

whereClause = "\"STATION_ID\" = 'CAP', 'DDM'"

So you want to select if station id is CAP or DDM?

I'm not sure you can comma separate those it might need to be

whereClause = "\"STATION_ID\" = 'CAP' OR "\"STATION_ID\"= 'DDM'"

See if that works

Or try:

whereClause = "\"STATION_ID\" IN ('CAP', 'DDM')"

IanMurray

whereClause = "\"STATION_ID\" = 'CAP', 'DDM'"

So you want to select if station id is CAP or DDM?

I'm not sure you can comma separate those it might need to be

whereClause = "\"STATION_ID\" = 'CAP' OR "\"STATION_ID\"= 'DDM'"

See if that works

Únete a la Conversación

Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.

Enlaces Rápidos

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