Cant seem to make this query work

Hi,

I have a query thatI need to make into one query for the sake of an application reading only one cursor.

Here's the syntax:

select (select distinct(x.amount) from escrow k inner join e120 x on k.escrow = x.escrow
inner join a10 g on x.escrow = g.escrow where k.ftype = 'S' group by x.amount, g.officer) As New,
a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled

from a10 a
inner join escrow d on a.escrow = d.escrow
inner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21' and j.fstatus = 'PAID' group by a.officer

The error message i'm recieving is the following:

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

Any ideas? any suggestions would be great.

Thanks

This error came from the only subquery in your query (which produces the New column). You're trying to return a result set 'distinct(x.amount)' and then use it as an expression, which is not permitted. Because SQL can not predict how many rows will be returned by the subquery, and there is no relationship between the column New by the subquery and other columns in the whole query, thus SQL can't join theNew column to the whole result set as other columns. In this case, you should either make the subquery return a single value:

select (select max(distinct(x.amount)) from escrow k inner join e120 x on k.escrow = x.escrow
inner join a10 g on x.escrow = g.escrow where k.ftype = 'S' group by x.amount, g.officer) As New,
a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled

from a10 a
inner join escrow d on a.escrow = d.escrow
inner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21' and j.fstatus = 'PAID' group by a.officer

or move the tables referenced by the subquery and find join conditions with other tables, then returned a column data which satisfies the join conditions, rather than using subquery.

|||

Hi when I run the query I get the following error:

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Warning: Null value is eliminated by an aggregate or other SET operation.

Is there a substitute for the = operator I can use to get this to parse correctly?

Thanks,

|||

Strange What's the query you're using? Are you sure you add a MAX to make the subquery return single value?

selectmax(distinct(x.amount))