coldfusion count case error depending on datasource

1
votes

I'm using coldfusion to retrieve some values from a database using a COUNT CASE statement.

The SQL below works perfectly well against a SQL server datasource:

<cfquery name="getData" datasource="cfTSP1_Dev">
  SELECT  SiteFK,
      Scheme,
      COUNT(case when DrawingTypeFK = 'Civil' then 1 else null end) as CCount,
      COUNT(case when DrawingTypeFK = 'Mechanical' then 1 else null end) as MCount,
      COUNT(case when DrawingTypeFK = 'Electrical' then 1 else null end) as ECount,
      COUNT(case when DrawingTypeFK = 'P & ID' then 1 else null end) as PCount,
      COUNT(case when DrawingTypeFK = 'Zoning' then 1 else null end) as ZCount
  FROM     Drawings.tblDrawingsData
  where     SiteFK = 'MYSITE'
  group by  SiteFK,Scheme
  order by Scheme

but when I change the query to point at exactly the same table in an access database I receive an error:

<cfquery name="getData" datasource="drawingsLink">
  SELECT  SiteFK,
      Scheme,
      COUNT(case when DrawingTypeFK = 'Civil' then 1 else null end) as CCount,
      COUNT(case when DrawingTypeFK = 'Mechanical' then 1 else null end) as MCount,
      COUNT(case when DrawingTypeFK = 'Electrical' then 1 else null end) as ECount,
      COUNT(case when DrawingTypeFK = 'P & ID' then 1 else null end) as PCount,
      COUNT(case when DrawingTypeFK = 'Zoning' then 1 else null end) as ZCount
  FROM     tblDrawingsData
  where     SiteFK = 'MYSITE'
  group by  SiteFK,Scheme
  order by Scheme

The error being this:

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'COUNT(case when DrawingTypeFK = 'Civil' then 1 else null end)'.

Any ideas why? My only theory is that its the access driver but my other queries using this datasource (without Count Case) are fine.

Cheers Neil

2
sql-servercoldfusioncountms-access-2007case
Because...Access. Stop using Access, it is not built to power a web app, use something like MySQL or PostgreSQL. They are both free, and much more powerful and properly suited to support a web application than Access. – Scott Stroz
In all seriousness, I would hazard a guess this is an issue with the JDBC driver for Access. – Scott Stroz
I cannot speak for newer versions of Access, but the last time I used it, CASE was not supported from SQL. As a desktop program, it is limited. Granted that was a long time ago, but it may be the cause here, rather than a driver issue. To verify it, remove CF from the equation and run the SQL directly in Access. Honestly though, even MS recommends against using Access in multi-threaded web applications. As Scott said, it is just not designed for it. – Leigh
Not related to your question, but you might have a logic error. Whenever I write a query like yours, I use sum(case ...). – Dan Bracuk
In this specific case, using sum(case when .. then 1 else 0 end) instead should not make any difference in the end result. That said, I tend to use sum as well. – Leigh

2 Answers

1
votes

Access' db engine does not support CASE ... WHEN, so you'll need to rewrite the query. You can use IIf() in Access SQL to approximate CASE ... WHEN.

SELECT  SiteFK,
  Scheme,
  Count(IIf(DrawingTypeFK = 'Civil', 1, null)) as CCount

Count() counts only non-Null values, so that should give you what I think you want. However if you want to use Sum(), as suggested in comments, this should also work:

Sum(IIf(DrawingTypeFK = 'Civil', 1, 0)) as CCount
0
votes

I haven't test this code and I imagine it will be much less efficient than your code, but it should work in MS Access. If your code worked everywhere you needed, I would say it is pereferable to the code below.

SELECT
        SiteFK,
        Scheme,
        (
            SELECT  count(*)
            FROM    Drawings.tblDrawingsData Civil
            WHERE   Civil.Scheme = tblDrawingsData.Scheme
                AND Civil.DrawingTypeFK = 'Civil'
        ) AS CCount,
        (
            SELECT  count(*)
            FROM    Drawings.tblDrawingsData Civil
            WHERE   Civil.Scheme = tblDrawingsData.Scheme
                AND Civil.DrawingTypeFK = 'Mechanical'
        ) AS MCount,
        (
            SELECT  count(*)
            FROM    Drawings.tblDrawingsData Civil
            WHERE   Civil.Scheme = tblDrawingsData.Scheme
                AND Civil.DrawingTypeFK = 'Electrical'
        ) AS ECount,
        (
            SELECT  count(*)
            FROM    Drawings.tblDrawingsData Civil
            WHERE   Civil.Scheme = tblDrawingsData.Scheme
                AND Civil.DrawingTypeFK = 'P & ID'
        ) AS PCount,
        (
            SELECT  count(*)
            FROM    Drawings.tblDrawingsData Civil
            WHERE   Civil.Scheme = tblDrawingsData.Scheme
                AND Civil.DrawingTypeFK = 'Zoning'
        ) AS ZCount
  FROM      Drawings.tblDrawingsData
  WHERE     SiteFK = 'MYSITE'
  GROUP BY  SiteFK, Scheme
  ORDER BY  Scheme

This is a correlated subquery, so it could be very slow. Buyer beware.