Subquery returned more than 1 value. This is not p

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 2005 Forums

Transact-SQL (2005)

Subquery returned more than 1 value. This is not p

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-01-22 : 05:14:18

Hi,

I have a table called MomClassAppointment with a field called CancellationDate datetime.
I want to return all the customers whose CancellationDate is null or whose CancellationDate happened more than 2 days ago. The result set retuns a few results therefore i get the error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How can I solve it?
Thanks

PROCEDURE [dbo].[UnAssigned]

as
declare @NumberOfDays as int

set @NumberOfDays =(SELECT DATEDIFF(day, CancellationDate, GETDATE()) FROM
[ChildbirthClasses].[dbo].[MomClassAppointment] )

SELECT c.ClassId
,MID
,ClassNumber
, CancellationDate
,(case when CancellationReason2 is null then '' else CancellationReason2 end) CancellationReason2
,FirstName + ' ' + FamilyName AS CustomerName
,@NumberOfDays as NumberOfDays
,Telephone
,Cellular
from dbo.MomDetails M
left JOIN dbo.MomClassAppointment A ON M.ID=A.ID
left join dbo.Classes c ON A.Classid=C.CLASSID
left join dbo.ClassStatus s on s.StatusId=c.StatusId
left JOIN dbo.CancellationReason CR ON CR.CancellationReasonId=A.CancellationReasonId
where a.classid is null or a.classid=0 and
(@NumberOfDays>=3 or @NumberOfDays is null)
order by PregnancyWeek asc
return

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 05:18:01

set @NumberOfDays =(SELECT DATEDIFF(day, CancellationDate, GETDATE()) FROM
[ChildbirthClasses].[dbo].[MomClassAppointment] )

select @NumberOfDays = sum(DATEDIFF(day, CancellationDate, GETDATE())
FROM [ChildbirthClasses].[dbo].[MomClassAppointment]

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-22 : 05:18:57

[code]PROCEDURE [dbo].[UnAssigned]

as
declare @NumberOfDays as int

SELECT
c.ClassId
,MID
,ClassNumber
, CancellationDate
,(case when CancellationReason2 is null then '' else CancellationReason2 end) CancellationReason2
,FirstName + ' ' + FamilyName AS CustomerName
,@NumberOfDays as NumberOfDays
,Telephone
,Cellular
from dbo.MomDetails M
left JOIN dbo.MomClassAppointment A ON M.ID=A.ID
left join dbo.Classes c ON A.Classid=C.CLASSID
left join dbo.ClassStatus s on s.StatusId=c.StatusId
left JOIN dbo.CancellationReason CR ON CR.CancellationReasonId=A.CancellationReasonId
where
(a.classid is null or a.classid=0) and
(A.CancellationDate is NULL or DATEDIFF(day, A.CancellationDate, GETDATE()) >2)
order by PregnancyWeek asc
return [/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 05:26:39

This might perform better (if necessary index are present).
Also the right records are fetched.

CREATE PROCEDURE [dbo].[UnAssigned]
as

SELECT		c.ClassId,
		MID,
		ClassNumber,
		CancellationDate,
		isnull(CancellationReason2, '') as CancellationReason2,
		FirstName + ' ' + FamilyName AS CustomerName,
		datediff(day, A.CancellationDate, getdate()) as NumberOfDays,
		Telephone,
		Cellular
from		dbo.MomDetails as M
left JOIN	dbo.MomClassAppointment as A ON M.ID=A.ID and a.classid=0 and A.CancellationDate > DATEADD(day, -2, getdate())
left join	dbo.Classes as c ON A.Classid=C.CLASSID
left join	dbo.ClassStatus as s on s.StatusId=c.StatusId
left JOIN	dbo.CancellationReason as CR ON CR.CancellationReasonId = A.CancellationReasonId
order by	PregnancyWeek

Don't forget to prefix all your columns with table alias.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources