CODEWITHSUNDEEP
sqlsql-serversql-server-2005select
Ezi
Ezi

Reputation: 2210

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 VARCHAR(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.

Upvotes: 0

Views: 81

Answers (3)

codingbadger
codingbadger

Reputation: 44014

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

Upvotes: 3

Marc B
Marc B

Reputation: 360762

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.

Upvotes: 2

AD.Net
AD.Net

Reputation: 13399

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).

Upvotes: 3

Related Questions