Complex SQL - failed to parse

[douglascoleman]

I have a complex SQL stmt that uses the PIVOT command.  This SQL stmt executes fine in the SQL query tool and I have verified it is coded properly in the client - but if failes to parse, I am suspecting the SLX driver cannot handle?  We are on the fat client version of 7.5.  Does anyone have any suggestions - we have remote users so I cannot point directly to the database.

Thanks

[DanCarvin]

One way to get around this is to write your query in a way the provider can handle.  Sometimes something like

SELECT FIELDS FROM TABLE WHERE TABLEID IN (SELECT T.TABLEID FROM TABLE T INNER JOIN ....

will work.

Another thing I like to do is create SQL views of summarized data and aim the provider at that view.     That can be an issue for remotes because there isn't a good way to install SQL views on remotes other than by cutting new DBs.

Another solution that worked for me recently in one situation was to create a static table (the data in it will not change) in a test database, add the data, and have the provider work with that.   I can bundle the new table with the "include data" option, install it on production and the new table with data will sync to remotes along with the plugin that works with it.