AnsweredAssumed Answered

MakeQueryTable errors and very weird behaviour

Question asked by jhchan69 on Jan 5, 2012
Latest reply on Jan 10, 2012 by geonetadmin
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? is my original script is my test one that magically fixes something to allow the first to work.