Subquery returned more than 1 value error message

Question

Post reply

Subquery returned more than 1 value error message

Canazei65

SSCommitted

Points: 1696
More actions
August 6, 2013 at 5:33 am

#296473

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 1

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

Canazei65 SSCommitted Points: 1696 More actions · Answer 2

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Canazei65 SSCommitted Points: 1696 More actions · Answer 4

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.