Can anyone tell me what's wrong in this portion of a SQL statement? The
relevant fields are simple integers and char(1)

This works fine:


CONVERT(SMALLINT, SUM(CASE WHEN a.field1 = 1 AND a.field2 = 0 THEN 1 ELSE 0
END)) AS count1,


And this works fine:

CONVERT(SMALLINT, SUM(CASE WHEN a.field2 = 0 AND a.field3 <> ' ' THEN 1 ELSE
0 END)) AS count2,


But this gets error "COUNT field incorrect or syntax error"

CONVERT(SMALLINT, SUM(CASE WHEN a.field1 = 1 AND a.field2 = 0 AND a.field3
<> ' ' THEN 1 ELSE 0 END)) AS count3,


This gets the same:

CONVERT(SMALLINT, SUM(CASE WHEN (a.field1 = 1) AND (a.field2 = 0) AND
(a.field3 <> ' ') THEN 1 ELSE 0 END)) AS count3,


As does this:

CONVERT(SMALLINT, SUM(CASE WHEN (a.field1 = 1 AND a.field2 = 0 AND a.field3
<> ' ') THEN 1 ELSE 0 END)) AS count3,


It seems simple enough to me, so obviously I'm missing something.

Can you post the whole statement?  It's hard to debug a snippet of code
- the syntax error may be on the previous line or the next line.  For
example, where does "COUNT" come into play?  It's not part of this
snippet you posted.

Show quoteHide quote

Thanks for your answer; sorry for my delayed response. I was out of town.

Yes, the COUNT error message was puzzling. It turns out that the error message was wrong; I had actually mistyped a field name.

Thanks for pointing me in the right direction.



  Can you post the whole statement?  It's hard to debug a snippet of code - the syntax error may be on the previous line or the next line.  For example, where does "COUNT" come into play?  It's not part of this snippet you posted.

  --
  mike hodgson
  http://sqlnerd.blogspot.com



  Paul Pedersen wrote:
Can anyone tell me what's wrong in this portion of a SQL statement? The
relevant fields are simple integers and char(1)

This works fine:


CONVERT(SMALLINT, SUM(CASE WHEN a.field1 = 1 AND a.field2 = 0 THEN 1 ELSE 0
END)) AS count1,


And this works fine:

CONVERT(SMALLINT, SUM(CASE WHEN a.field2 = 0 AND a.field3 <> ' ' THEN 1 ELSE
0 END)) AS count2,


But this gets error "COUNT field incorrect or syntax error"

CONVERT(SMALLINT, SUM(CASE WHEN a.field1 = 1 AND a.field2 = 0 AND a.field3
<> ' ' THEN 1 ELSE 0 END)) AS count3,


This gets the same:

CONVERT(SMALLINT, SUM(CASE WHEN (a.field1 = 1) AND (a.field2 = 0) AND
(a.field3 <> ' ') THEN 1 ELSE 0 END)) AS count3,


As does this:

CONVERT(SMALLINT, SUM(CASE WHEN (a.field1 = 1 AND a.field2 = 0 AND a.field3
<> ' ') THEN 1 ELSE 0 END)) AS count3,


It seems simple enough to me, so obviously I'm missing something.

How can I check if it is currently executing?
killed/rollback stuck on object_name(99)
Left Joins (*=)
IN operator versus BETWEEN and >= AND <=
Pass two parameters in query