Hi Kirk,
Enclose department condition in parenthesis or use IN operator
and (deptid = 1 or deptid = 23)
and depid IN (1,23)
I think, you can also remove grouping (GROUP BY ct.SpecDescin ).
>I am using a vfp user dialog to build a query string. The user has a list box where they can select multiple departments to use in the query. Then I build the dynamic query. Right now I am pasting it to the clipboard and running it in the query analyser for testing. The problem arises when I select more than one department. If I just select one, I get the desired results (Number of cases by speciality for each month). But if I choose more than one department, it get the following error:
>
>
>Server: Msg 512, Level 16, State 1, Line 1
>Subquery returned more than 1 value.
>This is not permitted when the subquery follows =, !=, <, <= , >, >=
>or when the subquery is used as an expression.
>
>
>I understand what it is telling me, but I don't know how to fix it.
>Here is the query that is generated when multiple departments are selected:
>
>
>Select c.SpecDesc, isnull((select COUNT(ct.SpecDesc)
>FROM dbo.meetings m1 INNER JOIN
>coSpec ct ON m1.specid = ct.specid
>where (datepart(mm,m1.begintime)=1) and m1.specid=m.specid and datepart(yyyy,m1.begintime)=2002
>and m1.iscompleted=1 and m1.iscancelled=0
>and deptid = 1 or deptid = 23
>GROUP BY ct.SpecDesc),0) as JAN,
>
< snip >>
>
>
>
>Thanks in advance for any help.
>Kirk
--sb--