[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 996
  • Last Modified:

Subquery returned more than 1 value

I have Query with 2 sub queries and I get this error.

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.


The query is:

select MA.serial_number,
MA.transaction_date as ACQ_DATE, 
MA.T_From as ACQ_FROM, 
MA.NOTES as ACQ_NOTES,
(select top 1 MD.transaction_date from DISP_VIEW MD where MD.serial_number = MA.serial_number
and MD.transaction_date > MA.transaction_date order by md.transaction_date) as DISP_DATE,
(select top 1 MD.TRANSFER_TO from DISP_VIEW MD where MD.serial_number = MA.serial_number
and MD.transaction_date > MA.transaction_date order by md.transaction_date) as DISP_To
from ACQ_VIEW MA

Open in new window


I have determined that it is this sub query that is causing it:

(select top 1 MD.TRANSFER_TO from DISP_VIEW MD where MD.serial_number = MA.serial_number
and MD.transaction_date > MA.transaction_date order by md.transaction_date) 

Open in new window


This first subquery works fine, but it is puzzling me why the second errors out.
0
mossmis
Asked:
mossmis
1 Solution
 
brad2575Commented:
You may be getting 2 of the same value returned

Try adding group by MD.Transfer_TO to your statement.
0
 
lcohanDatabase AnalystCommented:
The problem must be in the view from DISP_VIEW MD in my opinion as TOP 1 must ensure a single record(value) is returned.

Can you post the code from the view DISP_VIEW MD as well?
0
 
mossmisAuthor Commented:
the DISP View was the issue. It had too much code to post. I rewrote it and I got it to the merge query to work. Thanks!
0
 
nishant joshiTechnology Development ConsultantCommented:
please check below query ,is below query works according to your requirement?
select MA.serial_number,
MA.transaction_date as ACQ_DATE, 
MA.T_From as ACQ_FROM, 
MA.NOTES as ACQ_NOTES,
MD.transaction_date,MD.Transfer_TO
from ACQ_VIEW MA
JOIN (select top 1 MD.transaction_date,MD.Transfer_TO from DISP_VIEW MD order by md.transaction_date) MD
ON MD.serial_number = MA.serial_number
and MD.transaction_date > MA.transaction_date

Open in new window


Thanks,
Nishant
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now