August 6, 2013 at 5:33 am
Hi
I'm receiving the following message:
Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression
So I'm guessing that in one of the sub-queries, I'm returning more than 1 row of data. Problem is that after dissecting this for hours, I can't find where. I can't see where I can replacing an '=' with 'IN', 'MAX', 'Top' etc. might be appropriate either.
Strangely when I run the code against another schema in the same database it works fine.
Help please!
The code is:
declare @Locality varchar(max)
set @Locality = 'Locality Yate'
SELECT T.Locality,GS2.CodeDescription AS 'Specialty',Status,Count(T.ClientID) AS 'Number'
FROM (SELECT REF.ClientID,REF.ReferralNumber,SpecialtyReferredTo,ServiceTeam,
ISNULL((SELECT GS.CodeDescription
FROM dbo.vwSGReferrals As REF2
LEFT OUTER JOIN SchemaSG.GenServiceTeam AS GST ON GST.CodeDescription=REF2.ServiceTeam
LEFT OUTER JOIN SchemaSG.AmsSpecialtyTeams AS AST ON GST.Code=AST.Team
LEFT OUTER JOIN SchemaSG.GenSpecialty AS GS ON AST.Specialty=GS.Code
WHERE REF.ClientID=Ref2.ClientID AND REF.ReferralNumber=REF2.ReferralNumber
AND DischargeDateTime IS NULL
AND AST.Specialty Like '%LOC%'),'No Locality') AS 'Locality' ,
CASE WHEN (SELECT Max(Apps2.ContactID)
FROM ABI_RiO.dbo.vwSGReferrals Refs2
LEFT JOIN ABI_RiO.dbo.vwSGAppointmentsPD Apps2 ON Refs2.ClientID = Apps2. ClientID AND Refs2.ReferralNumber = Apps2.ReferralNumber
LEFT OUTER JOIN ABI_RiO.SchemaSG.AmsOutcome AS AOUT ON AOUT.Code=APPs2.Outcome
WHERE Refs2.ClientID = Ref.ClientID
AND Refs2.ReferralNumber = Ref.ReferralNumber
AND NationalCode=5) IS NOT NULL Then 'Active'
ELSE 'Waiting'
END As Status
FROM dbo.vwSGReferrals As REF
WHERE DischargeDateTime IS NULL
)T
LEFT OUTER JOIN SchemaSG.GenSpecialty AS GS2 ON T.SpecialtyReferredTo=GS2.Code
WHERE (ISNULL(Locality,'NULL') IN (SELECT * FROM fnSplitList(@Locality, ',')))
GROUP BY T.Locality,T.SpecialtyReferredTo,GS2.CodeDescription,Status;
Cheers
Tim
August 6, 2013 at 5:39 am
The first thing that comes to mind is the query
(SELECT GS.CodeDescription
FROM dbo.vwSGReferrals As REF2
LEFT OUTER JOIN SchemaSG.GenServiceTeam AS GST ON GST.CodeDescription=REF2.ServiceTeam
LEFT OUTER JOIN SchemaSG.AmsSpecialtyTeams AS AST ON GST.Code=AST.Team
LEFT OUTER JOIN SchemaSG.GenSpecialty AS GS ON AST.Specialty=GS.Code
WHERE REF.ClientID=Ref2.ClientID AND REF.ReferralNumber=REF2.ReferralNumber
AND DischargeDateTime IS NULL
AND AST.Specialty Like '%LOC%')
inside the ISNULL function.
Or otherwise the fnSplitList function is acting weird.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 6, 2013 at 5:57 am
Thanks Koen
That was my suspician too. Maybe one of those joins is not 1:1.
The split list function might be worth another look after that.
Cheers.
Tim
August 6, 2013 at 6:06 am
declare @Locality varchar(max)
set @Locality = 'Locality Yate'
SELECT
T.Locality,
[Specialty] = GS2.CodeDescription,
t.[Status],
[Number] = Count(T.ClientID)
FROM (
SELECT
REF.ClientID,
REF.ReferralNumber,
SpecialtyReferredTo,
ServiceTeam,
Locality = ISNULL((
SELECT TOP 1 GS.CodeDescription
FROM dbo.vwSGReferrals As REF2 -- You don't need this reference here; correlate
-- GST to REF instead of REF2 to REF
LEFT OUTER JOIN SchemaSG.GenServiceTeam AS GST
ON GST.CodeDescription = REF2.ServiceTeam
LEFT OUTER JOIN SchemaSG.AmsSpecialtyTeams AS AST
ON GST.Code = AST.Team
LEFT OUTER JOIN SchemaSG.GenSpecialty AS GS
ON AST.Specialty = GS.Code
WHERE REF.ClientID = Ref2.ClientID
AND REF.ReferralNumber = REF2.ReferralNumber
AND DischargeDateTime IS NULL -- which table alias?
AND AST.Specialty Like '%LOC%' -- this turns LEFT JOIN into INNER JOIN!
),'No Locality'),
[Status] = CASE
WHEN (
SELECT Max(Apps2.ContactID)
FROM ABI_RiO.dbo.vwSGReferrals Refs2
LEFT JOIN ABI_RiO.dbo.vwSGAppointmentsPD Apps2
ON Refs2.ClientID = Apps2. ClientID
AND Refs2.ReferralNumber = Apps2.ReferralNumber
LEFT OUTER JOIN ABI_RiO.SchemaSG.AmsOutcome AS AOUT -- is this table required?
ON AOUT.Code = APPs2.Outcome
WHERE Refs2.ClientID = Ref.ClientID
AND Refs2.ReferralNumber = Ref.ReferralNumber
AND NationalCode = 5 -- which table alias?
) IS NOT NULL Then 'Active'
ELSE 'Waiting' END
FROM dbo.vwSGReferrals As REF
WHERE DischargeDateTime IS NULL -- which table alias?
)T
LEFT OUTER JOIN SchemaSG.GenSpecialty AS GS2
ON T.SpecialtyReferredTo = GS2.Code
WHERE (ISNULL(t.Locality,'NULL') IN (SELECT * FROM fnSplitList(@Locality, ',')))
GROUP BY T.Locality, T.SpecialtyReferredTo, GS2.CodeDescription, [Status];
The splitlist function is likely to slow things up quite a bit.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 6, 2013 at 8:05 am
Hi Chris
Thanks for that. It works like a dream. I shall also implement your more elegant SQL against the other schemas as well.
Have a top day.
Cheers.
Tim.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy