Try putting quotes around table and field names such as: b.\"category\"
Without the quotes one of those names may turn out to be a SQL reserved word and this triggers the dreaded ? suntax error result.
Phil, Yeah, hadn't considered reserved words; double checked and there aren't any that I am using. Darn. That would have been simple. :)
S Chamblee, I went and tried using SQL Explorer. I added my table references and was able to do simple queries against a single table, but as soon as I tried to involve two tables I would get dreaded "?" response. So no luck there yet. I will keep trying.
Once you get two tables involved, you need a "join" clause specifying the relationship between them. And that is lacking in your original query.
I had tried manual inclusion of various forms of a JOIN. One of those forms is in the WHERE clause I used above: WHERE a.AB = b.BA. At least from the examples noted in the Missing Manual at http://www.filemakerhacks.com/?p=6406.
Here's some output from SQL Explorer that I have tried (and it fails with a question mark):
SELECT a."aaaID", b."aaAuthorID", b."aaID"
FROM "Courses" a
INNER JOIN "BooksByAuthors" b ON a."aaaID" = b."aaID"
This is about as simple as it can be made. I went ahead and renamed various fields to remove leading underscores, etc, but to no avail.
I am having the exact same problem. I have tested everything in http://www.seedcode.com/cp-app/ste_cat/sqlxfree and it works fine. The pasted formula in my database field simply returns the "?". Is there something I/we are missing in the way filemaker needs to display the returned data? Does it have to be in a portal, like in seedcode, or repetitions (although mine should return only one)? I look forward to suggestions as do you i'm sure...
YES! I got it figured out.
The problem was that the ID fields I was using in the two tables were of different TYPES: one was a number, the other was text. DOH!
Dealing with old architecture. :)
Sorry to hear you are having troubles. Our problems sound slightly different as I wasn't able to get SQL Explorer to work either (which I guess is a good thing); having it work in Explorer but then NOT in the live system is really odd.
There is nothing special about how the returned data needs to be handled. It is all in one data blob which you can assign to a field, a variable, whatever you can assign text-strings to. What I often do is use the Data Viewer to test/create my ESQL statements; you can view the return results right there easily. 2emPower's Dev Toolbox (or something like that) also has a nice built in 'Evaluate' function that you can use to run ESQL statements through; one huge benefit to this tool is that you will often get an error code back and not just a simple "?" response. The error code may not be helpful, but it is a little more helpful than "?". :)
Can you post your query or screen shots of SQL Explorer?