Can't believe this went unanswered. Well, this is the only reference I can find on the issue, and it's 2016 and I'm using PHP + ODBC to get data out of FMS14 and I'm getting the same results as the above is from 2012. If somwone has some insight, it would be greatly appreciated.
SELECT p.id, p.pto_year, p.days_year, p.days_used_c AS days_used, c.id AS contact_id, firstName, lastName FROM Contact c LEFT JOIN pto_days p ON c.id = p.id_contact WHERE c.manager_id IS NOT NULL AND c.status = 'active' AND pto_year = 2016 ORDER BY c.lastName ASC
Thanks for replying, Mike.
I had been reading through fm12_odbc_jdbc_guide:
RIGHT JOIN is supported, but the grammar RIGHT OUTER JOIN is not currently supported.
In the fm14_sql_reference:
RIGHT OUTER JOIN is not currently supported.
Hope I'm not crazy for being confused...
I suppose it wasn't in version 11.
I suggest you alter the query to a form that works and move on.
Yes, there is some reason - we switched to ODBC back when it was clearly faster than PHP API in our testing (I understand it's greatly improved) and we have a large web application written with a PHP framework that also talks to other databases. Much easier to let the framework abstract with ODBC driver than to have other non-FM team members have to use the PHP API. Makes switching the datasource between FM, MySQL, PostreSQL trivial.
We've had to use the API or the XML interface a few times over the last few years, but not much.
I crafted a work-around pre-populating some data. I know ODBC has never been too high a priority.
Thanks for getting back and the super fast response.
Do you mean you solved?
It had better to make another thread, since your query don't use RIGHT JOIN and you don't use FM11, only the same thing is error message...
Be aware: If you are in fact still using v11, there is a known issue with the ODBC listener process. After some number of requests, the process will crash. We've seen it here repeatedly, and it was acknowledged by Tech Support. Suggest upgrading to a more recent version (v11 is out of support anyway).