ERROR: "Subquery returned more than 1 value."

I've got a big procedure written by a contractor. I'm trying to
execute it (against a test db until I get it worked out) and there is
one section of it that fails. I've isolated the section and run it in
Query Analyzer and it still fails. Here is the SQL:
DELETE FROM tblBedOccupancy WHERE (
ContactID IN (
SELECT ContactID FROM tblPeople WHERE Community IN (
SELECT DISTINCT BuildingID FROM lnkCompaniesBuildings WHERE CompanyID
<> 61
)
)
)
The error returned is:
Server: Msg 512, Level 16, State 1, Procedure tg_DeleteOccupancyRecord,
Line 18
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
The statement has been terminated.
If I change DELETE to SELECT *, it runs fine. If I run the subqueries
they run fine. How can I get this to work?
Thanks,
Josh D> The error returned is:
> Server: Msg 512, Level 16, State 1, Procedure tg_DeleteOccupancyRecord,
> Line 18
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as
> an expression.
> The statement has been terminated.
Look closely at the error message. Did you notice the name
"tg_DeleteOccupancyRecord". What does this refer to? Looks like a
trigger - and a poorly written one to boot. The problem is the code in the
trigger.|||I'd agree with Scott, sounds like you have trigger written by someone who
didn't realize that triggers fire once per statement, not once per row.

> Server: Msg 512, Level 16, State 1, Procedure tg_DeleteOccupancyRecord,
> Line 18
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as
> an expression.
> The statement has been terminated.|||> I'd agree with Scott, sounds like you have trigger written by someone who
> didn't realize that triggers fire once per statement, not once per row.
...and if that was the contractor, this might constitute breach of contract
.
:)
ML|||> ...and if that was the contractor, this might constitute breach of
> contract.
> :)
Well, if the contractor's side of the agreement doesn't explicitly state, "I
know what I'm doing"...|||> Well, if the contractor's side of the agreement doesn't explicitly state, "Id">
> know what I'm doing"...
In continental law the "I know what I'm doing" part is presumed (praesumptio
iuris), and the contractor can only limit his own liability by stating the
opposite.
ML

error with subquery.....

I am getting an error from a query that that has a subquery.

Msg 512, Level 16, State 1, Line 2

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(0 row(s) affected)

This is a the query.

select *

from dhcp

where nameofcomputer = (SELECT p.nameofcomputer

FROM v_Pams_DHCP p

Left Outer Join adat2005_main a

ON p.nameofcomputer = a.nameofcomputer

where a.serialnumber is null)

Thanks in advance.

Gene

Use in operator,

Code Snippet

select

*

from

dhcp

where

nameofcomputer

in (

SELECT

p.nameofcomputer

FROM

v_Pams_DHCP p

Left Outer Join adat2005_main a

ON p.nameofcomputer = a.nameofcomputer

where

a.serialnumber is null

)

Exists Might be faster than IN,

Code Snippet

select

*

from

dhcp

where

Exists

(

SELECT

p.nameofcomputer

FROM

v_Pams_DHCP p

Left Outer Join adat2005_main a

ON p.nameofcomputer = a.nameofcomputer

where

a.serialnumber is null and p.nameofcomputer = dhcp.nameofcomputer

)

error with SQL statement

hi, i try the below sql code, however when i try to execute the command, it always give me the error 'Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.'

If i remove the "(SELECT [description] = CASE WHEN ([description]) <> '' THEN [description] ELSE [tbl_batch_completed].[status] END)" under the group by clause, it will work

do I need to change something in the sql statement?

Thanks in advance

SELECT TAG_FACE_CON.REQUEST_ID,
(SELECT [description] = CASE WHEN ([description]) <> '' THEN [description] ELSE [tbl_batch_completed].[status] END)AS status
FROM (TAG_FACE_CON RIGHT JOIN tbl_batch_completed ON TAG_FACE_CON.GROUP_ID = tbl_batch_completed.ID)
GROUP BY TAG_FACE_CON.REQUEST_ID,(SELECT [description] = CASE WHEN ([description]) <> '' THEN [description] ELSE [tbl_batch_completed].[status] END)

You are going to have to remove the select staement from the group by. Also in that SQl Staement there is no from clause... It does not know where to get the batch completed information.

I would look into create a temp table to create the first section as raw data, then when you need to do the group by run it as a query from the temp table.|||I think restructuring your query like this will solve your problem:

SELECT
 TAG_FACE_CON.REQUEST_ID,
 CASE
 WHEN [description] <> '' THEN [description]
 ELSE [tbl_batch_completed].[status]
 END AS status
FROM
 TAG_FACE_CON
RIGHT JOIN
 tbl_batch_completed ON TAG_FACE_CON.GROUP_ID = tbl_batch_completed.ID
GROUP BY
 TAG_FACE_CON.REQUEST_ID,
 CASE
 WHEN [description] <> '' THEN [description]
 ELSE [tbl_batch_completed].[status]
 END

Terri