Subquery returned more than 1 value Error

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Subquery returned more than 1 value Error

Author

Topic

ravininave
Posting Yak Master

111 Posts

Posted - 2011-12-22 : 20:37:52

[code]
Declare @AppMstId int
set @AppMstId =101
Select a.ScratchNo ,a.ScratchPass,(CASE WHEN a.JoinType=1 THEN 'Paid' ELSE 'Normal' END) as JoinType,
(Select AppmstRegNo from AppMast Where AppMstId = a.AppMstID) as RegNo,
(Select AppMstName from AppMast Where AppMstId = a.AppMstID) as RegName,
(Select AppmstRegNo from AppMast Where AppMstId = a.ReceivedFrom) as ReceivedFrom,
(Select AppmstRegNo from AppMast Where AppMstId = a.TransferTo) as TransferTo
FROM
scratchMast a
Where
OwnerID = @AppMstId
Order by AppMstID;

[/code]
It gives error subquery returned more than 1 value. What could be the solution.

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

sureshkk
Starting Member

21 Posts

Posted - 2011-12-23 : 00:48:53

This error will be displayed if your sub queries returns more then one for a given AppMstID.

If u want only first record from subquries then use top 1 in the subqueries.

Declare @AppMstId int
set @AppMstId =101
Select a.ScratchNo ,a.ScratchPass,(CASE WHEN a.JoinType=1 THEN 'Paid' ELSE 'Normal' END) as JoinType,
(Select top 1 AppmstRegNo from AppMast Where AppMstId = a.AppMstID) as RegNo,
(Select top 1 AppMstName from AppMast Where AppMstId = a.AppMstID) as RegName,
(Select top 1 AppmstRegNo from AppMast Where AppMstId = a.ReceivedFrom) as ReceivedFrom,
(Select top 1 AppmstRegNo from AppMast Where AppMstId = a.TransferTo) as TransferTo
FROM
scratchMast a
Where
OwnerID = @AppMstId
Order by AppMstID;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources