I'm currently building a suite of excel spreadsheets to run against
postgresql. Excel is a great tool, however they force you to use ms
query to bind your spreadsheets to the database. I have concluded that
ms query is garbage. It 'helps' you with your queries by parsing them
before they are sent to the database. If you try to use any features
that ms query does not understand, for example the ~* operator for text
searches, ms query will not allow you to return to the spreadsheet *if*
you use the parameterized version.
Example:
select * from i_hate_ms_query where postgresql ~* 'great'
Works fine but
select * from i_hate_ms_query where postgresql ~* [param1]
borks.
Also, complex query forms with subqueries or inline views will of course
completely blow the fuses of ms query. By the way, this problem is not
limited to postgresql, trying to cust complex to sql server will give
you similar headaches.
That being said, I discovered that by saving the spreadsheet as xml you
can edit the sql source inside the spreadsheet and do just about
anything you want with it. I understand that office 2003 has some new
ways to do this, but is there some simple thing that I am missing?
Merlin
