Error in query using WHERE IN

This is the query I try to run:

Select Status from [transaction] where TransactionID IN (select MAX(CAST(TransactionID AS VARC开发者_开发技巧HAR(36))), sum(debit) 
FROM [transaction]
WHERE  dbo.getday(StartSaleTime) >= '5/1/2011' and dbo.getday(StartSaleTime) <= '5/3/2011'  and Status > -1 And TransactionNo like 'EL%' And TransactionType = 4 
GROUP BY CustomerID, debit HAVING ( COUNT(CustomerID) > 1 ))

it returns this error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

It tells you exactly what is wrong in the error message. When using in you can only specify one column in the select list.

If you change your query to this, it should work fine.

Select Status from [transaction] where TransactionID 

IN (select MAX(CAST(TransactionID AS VARCHAR(36))) as [TransactionID]
FROM [transaction]
WHERE  dbo.getday(StartSaleTime) >= '5/1/2011' and dbo.getday(StartSaleTime) <= '5/3/2011'  and Status > -1 And TransactionNo like 'EL%' And TransactionType = 4 
GROUP BY CustomerID, debit HAVING ( COUNT(CustomerID) > 1 ))

You can specify multiple columns but only when using EXISTS not IN

You are selecting two things and trying to use that with IN(). You should select only the ID when trying to do where someId In(list of Ids).

Your subquery has to return only a single field. Right now you're returning two, so the overall query looks kinda like:

SELECT ... WHERE TransactionID IN ((a,b), (c,d), etc...)

SQL server doesn't known which column to use for the IN stuff, so it's complaining.