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

[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

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

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

[WilliamCraft · Accepted solution]

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

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

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

[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

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