Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    74

    Question 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 [42000] [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).

    Kamen
    Last edited by KamenG; 07-25-06 at 17:50. Reason: Icon

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Access SQL does not support the CASE statement. You can use IIf() as a replacement.
    Paul

  3. #3
    Join Date
    Dec 2005
    Posts
    74
    That is weird stuff... Everybody redefines their own rules when it comes to databases!
    Kamen
    Last edited by KamenG; 07-25-06 at 18:44.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I wouldn't have suggested it if you couldn't. Note I said the IIf() function, not the VBA If/Then statement.
    Paul

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by KamenG
    That is weird stuff... Everybody redefines their own rules when it comes to databases!
    why are you surprised?

    the nice thing about SQL standards is that there are so many to choose from!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •