Double check to make sure where you are viewing the calculation field from you have valid data in "id". I'd simply put the T05_CONTRIBUTORS::_fkPeopleID field on the layout as well and make sure it has data.
Sometimes I get caught up in troubleshooting the SQL, that I forget to look for the obvious and see if there is actually any data to return or if my search criteria is accurate :-)
It sounds like the SQL is working, but that there is no match for id. Make
sure it has data and that there is a matching record.
Sent from my mobile device... Please excuse typos.
If it works in the data viewer, the SQL syntax is ok. Might be something else. Are you doing this in a script? If so post the script and let us take a look.
I've had similar situations where the field indexes needed to be re-set. You may want to unindex T04_PEOPLE::Last and T04_PEOPLE::PEOPLE_ID and then re-index them.
If you do decide to do this process, I would first unindex those fields and then exit Manage Database. Then go back into Manage Database and re-index the fields. I say this because I am ignorant on when a field index actually changes. Does the actual change occur when making edits on the Storage tab or does it change when exiting Manage Database? I don't know, so I do the latter.
Does it still work in the data viewer if you are in a different context? Yes, I know that an ExecuteSQL statement should ingore contexts but the data viewer evalutes on context. If you picked a no related layout to your SQL query then does the data viewer.
The query "context" is not needed, but the "arguments" that are called with table/field DO need context when placed in the calculation dialog.
I don't know if this post to fmforums will help. <http://fmforums.com/forum/topic/88426-interesting-quirk-for-unrelated-field-reference-in-field-definition>
thanks Beverly, that was said much better than I could have. When I'm debugging an issue. I try to take eliminate all possibilities. So in this case my question what is unique to having the query run in the data viewer that is different than when it is in a calculation.
Probably that 'context' thingy... and :(. We don't have "under the hood" on ExecuteSQL(), so it's just test and speculation from us developers...
-- sent from myPhone --
Just to take the context thing out of the equation, hard code the ID and see if that works. Something like:
Let ( id = "102618" ;
WHERE PEOPLE_ID = ? ";
This will help point to whether you are dealing with a context issue or not. If the calc above works, then I'd say that when you are viewing the calc it doesn't have access to "T05_CONTRIBUTORS::_fkPeopleID". If the calc above doesn't work, then I'd say the actual data you are searching for (PEOPLE_ID in the T04_PEOPLE table) has a screwed up index (as Daniel suggests), or doesn't actually match "102618" (perhaps there is a space, or other whitespace character in that data?).
While we're on this topic on context, one thing to keep in mind is that you table must have a table occurrence. I know this sounds obvious, but I fell into this trap. Once I had a utility table that (virtual list, I believe) that did not require a layout and had no relations, so I did not have it on the Relationship Graph.
At the time, I thought that eSQL worked from the raw table, but it appears that it works from - or at least needs - a table occurrence.
if it's a NUMBER, I would not put quotes around the variable to be sure it's getting the correct value
Let ( id = 102618
it must have a table occurrence on the graph in the FILE making the sql call.
You cannot call a table in ExecuteSQL() that is not on the graph. With that, know that the base table IS what is queried, from the standpoint of Table Occurrence's relationship criteria as on the graph is ignored. For example, if you query (from a layout based on Parent) and call up related (child data) that may have constraints in the relationship (not just a parent key match) such as date range, then the ExecuteSQL will not use the constraint even if you call that table occurrence. It will think it's just asking to search within the full set of child records as if the table had no relationships. - that's what "no context" means
That is a helpful clarification, Beverly. Thanks.