MakeQueryTable errors and very weird behaviour

JoshuaChan · ‎01-05-2012

I am trying to extract info out of two related tables. Someone suggested using MakeQueryTable and after fiddling around with syntax for a day it finally created a virtual table.

The values I need from it are LEGAL_NAME, FIRST_NAME, LAST_NAME - all are strings. If LEGAL_NAME is empty I want to use FIRST and LAST name concatenated.

Problem is it keeps bombing when LEGAL_NAME is null. I converted the value to string and in cases where first and last name are null the script doesn't mind that. But if LEGAL_NAME is null it bombs with the error message:
RuntimeError: ERROR 999999: Error executing function.
The index passed was not within the valid range.

I wrote another script to test things out. I did the MakeQueryTable with just one of the two tables. I manually (in SQLPlus) queried out the foreign key value of interest and plugged it into my test script and voila: the name fields print out fine. Immediately after I ran my problem script WITHOUT MAKING ANY CHANGES and it now works as well. That makes no sense to me whatsoever as my test script doesn't perform any joins.

Any clue what might be going on here?
How do I fix it so that it works without having to go thru each record and re-run the two scripts each time?

test_query_table_for_client.py is my original script
test_query_table.py is my test one that magically fixes something to allow the first to work.

JoshuaChan · ‎01-09-2012

a colleague and I did more testing and came to the conclusion that MakeQueryTable must be very buggy.

If the first of 3 fields is null it will "shift" the values up so that the 2nd value will now become the first value.
If I 'force' the values to become string via the str(<object>) it will call that null field "None" but it will still not be retreivable using "Getvalue" unless I run my 2nd script first. However that only happens if I run the first script immediately after the 2nd.

So - in an effort to avoid using voodoo sorcery and alchemy to make my script work I'll probably try something else like a whole series of MakeTableView statements.

Anonymous User · ‎01-09-2012

Original User: csny490

A typical ESRI analysis work flow:
1. Copy all data local (aka: Don't do anything over a network connection).
2. Clean up the data as to avoid anything that might make ESRI software throw a rod (getting rid of nulls in your case!)
3. Do the analysis

JoshuaChan · ‎01-09-2012

A typical ESRI analysis work flow:
1. Copy all data local (aka: Don't do anything over a network connection).
2. Clean up the data as to avoid anything that might make ESRI software throw a rod (getting rid of nulls in your case!)
3. Do the analysis

Thanks for the best practices.
In this case I was hoping to avoid copying the data because the tables I need to use are 300K+ rows and 60K+ rows each.
As for the nulls, if I was able to select them programatically then I wouldn't have this problem in the first place. So I'd have to either export it to a DBF and do this in Access (also not preferred) or some other manual process.

Because I'm writing this script so that any non-GIS user can run it I'm afraid this option is out.

Anonymous User · ‎01-09-2012

Original User: csny490

As for the nulls, if I was able to select them programatically then I wouldn't have this problem in the first place

Can't you specify this as part of the query???

For example, something like:
"PARCEL_ID = PARCEL_ID AND LEGAL_NAME IS NOT NULL"

Are you using this tool as a simply query or as part of a many-to-one or many-to-many sort of thing?

Typically I use this tool only for many-to-one relates. For example, if I have many records in a table each describing the height of the trees in a particular polygon over time, where:

POLYGON_ID,DECADE,TREE_HEIGHT
1,1,10
1,2,15
1,3,30
1,4,60
1,5,100
1,6,110
1,7,115
1,8,120

and I want to return a polygon for each match record in the table.

JoshuaChan · ‎01-09-2012

Can't you specify this as part of the query???

For example, something like:
"PARCEL_ID = PARCEL_ID AND LEGAL_NAME IS NOT NULL"

Are you using this tool as a simply query or as part of a many-to-one or many-to-many sort of thing?

Typically I use this tool only for many-to-one relates. For example, if I have many records in a table each describing the height of the trees in a particular polygon over time, where:

POLYGON_ID,DECADE,TREE_HEIGHT
1,1,10
1,2,15
1,3,30
1,4,60
1,5,100
1,6,110
1,7,115
1,8,120

and I want to return a polygon for each match record in the table.

I guess the question is what does ArcGIS think a null text string is. Because I thought it was ok for "" or " " but when I got my error message it kept returning some blurb about a "None" datatype. I also tried using the "IS NULL" evaluation - also same error.

After converting this "None" datatype to string I was able to evaluate it as stringname = "None" in my 2nd checking script but in my original script it didn't work. However it did work properly if I ran script #2 first. Why is that other than script 2 missing the table join part of the MakeQueryTable statement?

When I check the field names in the join table they're all there. But I can't see any values even though the row counter gives me 1 row (as it should be)

Anonymous User · ‎01-09-2012

Original User: csny490

So the None type is a Python thing, and how Null values are interpreted in a cursor statement. For example:

searchRows = arcpy.SearchCursor(myTableView)
for searchRow in searchRows:
   if searchRow.getValue(myFieldName) == None:
      print "Null Value!"
del searchRow, searchRows

Not sure what the added utility is exacltly, but there is also a new .isNull cursor method in v10.0

searchRows = arcpy.SearchCursor(myTableView)
for searchRow in searchRows:
   if searchRow.isNull(myFieldName):
      print "Null Value!"
del searchRow, searchRows

So is your error occuring in the MakeQueryTable part or the cursor part? More specifically, what line # is your code bombing on?

JoshuaChan · ‎01-10-2012

So the None type is a Python thing, and how Null values are interpreted in a cursor statement. For example:
searchRows = arcpy.SearchCursor(myTableView)
for searchRow in searchRows:
   if searchRow.getValue(myFieldName) == None:
      print "Null Value!"
del searchRow, searchRows
Not sure what the added utility is exacltly, but there is also a new .isNull cursor method in v10.0
searchRows = arcpy.SearchCursor(myTableView)
for searchRow in searchRows:
   if searchRow.isNull(myFieldName):
      print "Null Value!"
del searchRow, searchRows
So is your error occuring in the MakeQueryTable part or the cursor part? More specifically, what line # is your code bombing on?

The test_query_table_for_client.py seems to run all the way up to

print "legal:" + legal

after that it bombs.

But as I mentioned before, if I run the other script first (test_query_table.py), then re-run test_query_table_for_client.py, the script runs without error and returns the correct results.

Anonymous User · ‎01-10-2012

Original User: csny490

Hmm...

Try inserting this code after you make the query layer:

nameDict = {}
errorDict = []
i = 0
searchRows = arcpy.SearchCursor("testtab")
for searchRow in searchRows:
   i = i + 1
   tlegal = row.WHSE_TANTALIS_TA_INTERESTED_PARTIES_LEGAL_NAME
   tfirst = row.WHSE_TANTALIS_TA_INTERESTED_PARTIES_FIRST_NAME
   tlast = row.WHSE_TANTALIS_TA_INTERESTED_PARTIES_LAST_NAME
   if tlegal != None:
      nameDict = str(tlegal)
   elif tfirst != None and tlast != None:
      nameDict = str(tfirst) + " " + str(tlast)
   else:
      print "Error in row #" + str(i)
      nameDict = "FUBAR"
      errorDict = [tlegal, tfirst, tlast]
del searchRow, searchRows
print errorDict

If you do get some FUBARS.... what row # are they occuring on? What are the legal, first, last variable values??? Print the contents of errorDict

One thing I notice is that you never delete your cursor objects in your code (see the last line of my code).... This is step is critical, since otherwise your cursor-based variables never re-initialize (even though you think they are). Also, be sure to restart your Python IDE evertime it errors until you get a feeling for how it works. When I first started doing Python stuff I did not do these two things, and it often led me to belive that Python/the gp object (now arcpy) was acting weird and inconsistently. It helps tremendously if you know you are starting from scratch everytime and your variables aren't using "ghost" values from previous runs.