query engine error

Can anyone tell me why I am getting this error when I try to view my report:

Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][SQL Sever] Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=,>,>= or when the subquery is used as anThis is a problem in the SQL-query.

Like the error says, the query has a subquery that returns multiple values (more than one row and column). Subqueries like that can't be in the column list or after the listed operators.

Make sure that the subquery returns only one value.

For example, next query won't work, as the subquery would return many rows (assuming there are many rows in the Suppliers-table):
SELECT ProductID, (SELECT SupplierID FROM Suppliers)
FROM Products

Similarly, the next query will cause the same error:
SELECT ProductID

FROM Products
WHERE SupplierID = (SELECT SupplierID FROM Suppliers)

The solution is to make the subquery to return only one value using WHERE or ORDER BY + TOP 1 statements, for example.|||

Sometime this might fit your need to limit the returned value to only a single on. Sometime you might WANt to return more than one value, then you will either have to use a JOIN, correlated query, or the IN operator like

Similarly, the next query will cause the same error:
SELECT ProductID
FROM Products
WHERE SupplierID IN (SELECT SupplierID FROM Suppliers)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||True. There are lot's of possibilities what he wants to achieve. A correlated subquery could be one alternative, too. Hard to tell without seeing the original query or requirement first :)