One thing you're missing is taking advantage of the ? placeholder.
- ExecuteSQL ( "SELECT PHONE.phoNumber FROM PHONE WHERE PHONE.phoID = ?" ; "" ; "" ; 11)
Have you tried
ExecuteSQL ( "
WHERE phoID = ?
" ; "" ; "" ; 11
Using the optional parameter (argument) will ensure that the value you're passing is correctly quoted (or not, as it were).
Changing the setup as your request.
THIS ONE WORKS FINE...
ExecuteSQL ( "SELECT PHONE.phoNumber FROM PHONE WHERE PHONE.phoType = ?" ; "" ; "" ; "Fax")
THIS ONE DOES NOT WORK...
ExecuteSQL ( "SELECT PHONE.phoNumber FROM PHONE WHERE PHONE.phoID = ?" ; "" ; "" ; 11)
The result is a.... ?
NOR DOES THIS ONE... (just put 11 in quotes although it shouldn't be because it is an interger/number)
ExecuteSQL ( "SELECT PHONE.phoNumber FROM PHONE WHERE PHONE.phoID = ?" ; "" ; "" ; "11")
The result is a.... ?
So my conclusion is that it is not a syntax thing I am doing wrong. For whatever reason the statement executes the where clause on the phoType field but it won't on the phoID field for whatever reason.
I tried it on another number field and it didn't work but it seems to work on text field types. So it works on phoType (text), phoStatus (text), Does not work on phoID (number, indexed) and phoArea (number). The calculation where the SQL script is being run is a calculation field set as "text" because the phoNumber is a phone number set as text (800-555-1212) which is the field I am calling and will be the result when successful.
Is there perhaps a common or novice mistake I am making in regards to dealing with numbers vs text when making these statements / SQL calls?
If you put EvaluationError() around the ExecuteSQL() what do you get?
Also try quoting the field name in case it is reserved word.
-- sent from myPhone --
Thanks for the suggestion on the EvaluationError(). I didn't know about that function to troubleshoot. I did as you asked on on the phoID field I received "8310" which when I look it up it says this... "There is an error in the syntax of the query." I don't really understand as the only thing I am changing is value in the parameter field, the TABLE.FIELD NAME and attempted to quotes, no quotes etc. Only thing that is consistent is any field that has a number type doesn't seem to work.
The other field phoArea which is also a number type field comes out to 8309. I could not yet find out what that code is. Again, the only thing that change was the value in that parameter, quotes and no quotes and the TABLE.FIELD.NAME.
I did also attempt to quote the field name to make sure these are no reserved words and that didn't affect the error code.
The fields that are text when testing the SQL statement send an error of 0 which means there is nothing wrong and it is working.
I appreciate the help, just kind of lost on what could be causing this.
If you use ExecuteSQL function in field calculation, check the result type is "text".
Or try to evaluate it in the DataViewer, this could give you a more descriptive message.
It needs to be wrapped in a Let() statement.
sql = ExecuteSQL ( "SELECT phoNumber FROM phone WHERE phoID=?" ; "" ; "" ; 11 );
BTW if you're querying a single table you don't need to include table name with the field name.
In this example I changed phoNumber to phonNumber. The error description appears after you hit "Monitor".
OK, I have no idea what the fix was. I just replaced the fields and closed out of the database and now it is working. I must have had some odd character or it was a fluke or somehow fixed a mistake without knowing it. In any case, your help was appreciated. Thanks for all the input.
I now have a very strong handle on troubleshooting these in the future and became very familiar with the syntax.
The more I use FM the more I love it.