I’m getting ‘Subquery returned more than 1 value’, without a subquery on my SELECT

I have a view in which I don’t have access into modify it, and I have to make a SELECT query that brings me the rows in witch a column is empty (empty, not null)

In this view I have:

Oid| Name | Email | Phone 

The total records on this view are more than 980,000 rows.

This is what I tried:

SELECT DISTINCT * FROM View WHERE Phone = '' 

SELECT DISTINCT * FROM View  WHERE datalength(Phone)<1

SELECT DISTINCT * FROM View  WHERE Len(Phone)<1

In all of them I get the error:

[Err] 21000 - [SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

If I make a SELECT without trying to get the empty rows form ‘phone’ it works, I also tried checking if some Oid were repeating with:

SELECT
    Oid, COUNT(*)
FROM
    View
GROUP BY
    Oid
HAVING 
    COUNT(*) > 1

But I get no result (no repetition)

The interesting thing is that if I make a TOP 1000 (finding the empty in phone), it works, so I don’t get what is happening.

What I’m doing wrong?

Answer

As the comments suggest the issue here will be in the view. What may not be immediately obvious is that the execution plans for reading from a view can vary wildly depending on what conditions you apply. In this case I’m assuming Phone is generated by a sub query. The view will assume that the sub query will only return one row. If you query the view without phone, the sub query is never executed.

If phone is required then the sub query is executed and hence causes the error.

Try running these with the execution plans switched on and that should help you understand where the issue lies.

This is functionality that you can make good use of, I use it regularly to conditionally join on tables dependant on parameters.