Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jung1975
Aged Yak Warrior
503 Posts |
 Posted - 2006-02-06 : 14:14:40
|
| I am keep getting an error says:"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."Can you tell me what I am doing wrong? Below is the SQL.SELECT distinct(dateprocessed), convert(varchar(10), [DateProcessed],121 )+ ' $' + convert(varchar(10), convert(Decimal(10,2), sum(CheckAmount)))+ ' ' + (convert(varchar(4),(select count(distinct(a.id)) from cas.idxcddata a join cas.idxcdimage b on a.transactionid = b.transactionidwhere documenttype = 2group by dateprocessed ))) as CheckAmtfrom cas.IDXCDdata b left join cas.IDXCDImage c on b.transactionid = c.transactionid WHERE Appliedcash = 0 and DocumentType =2group by DateProcessed |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-02-06 : 14:32:00
|
| your subquery is returning a value for each dateprocessed. Try replacing "group by dateprocessed" with "and dateprocessed = b.dateprocessed".orchange the subquery to a derived table and join it to your main table by processed date.Be One with the OptimizerTG |
 |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2006-02-07 : 07:00:25
|
| Replace "distinct(dateProcessed)" with just "DateProcessed" in main query. In subquery do what TG suggested. |
|
|
|
|
|
|
Subquery problem