Unanswered: SQL query fails w/Access but works with MS SQL Server
I am getting a failure with a SQL query executed against an Access *.mdb file using the latest ODBC driver (4.0) with the following message:
ERROR  [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(COUNT(*)=13) AND SUM (CASE WHEN ChannelID IN(0,1,2,3,4,5,6,7,8,9,10,11,12) THEN 1 END) = 13'.
The full text of the SQL command is as follows:
SELECT SchemaID FROM DlSchemaTable GROUP BY SchemaID HAVING(COUNT(*)=13) AND SUM (CASE WHEN ChannelID IN(0,1,2,3,4,5,6,7,8,9,10,11,12) THEN 1 END) = 13
There is nothing special about the database schema (I could reproduce the problem easily with a simple database).
This particular query (and some background about what/why/how/etc.) can be found in this thread in the "SQL" forum. I'd be very grateful for any insights as to why it is failing with MS Access when it works perfectly fine with MS SQL Server (and is good, ANSI SQL).
Last edited by KamenG; 07-25-06 at 17:50.