Simple SubqueryQuery Returning More than One Record

[jfield817]

Under SS 7.0
The code fragment below produces a predictable error on our data since the query returns more than one record

select X from Tablename where
1 = (select X from Tablename)


[Microsoft][ODBC SQL Server Driver][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.

However not all of our servers seem to be generating
the error .... Any reason why the above code
fragment would not consistently blow up ..

Thanks
John Field

 

[arrowhouse]

Do the servers that work OK only have one row in Tablename?
(I.E. the subquery is only returning one row)

Is the content of Tablename the same on all servers?

 

[jfield817]

I'm fairly sure they have multiple rows ....and
the original developer claims the script works on his server ...... but I am waiting from him for further confirmation ...
Are there any database settings that could turn off that error in advance ...

thanks
john

 

[balves]

It must be the difference in the data.

This will blow up with the error you detailed if the SELECT returns more than 1 row:

1 = (select X from Tablename)

To make it run with multiple rows (but perhaps not make sense), change the '=' to IN

 

[aalmeida]

You can use the agregate functions TOP 1, Max and Min to make the suquery return only one row.
There is no setings on SQL server that would make it to stop returning the error it will give you the error every time it would return more than one row, although you will not see it poping up if it returns one row or it is situated at flow Control part of the code like IF EXISTS ().
I hoed it helps AL Almeida
NT/DB Admin
&quot;May all those that come behind us, find us faithfull&quot;