CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Unknown
Unknown

Reputation: 155

Error: Subquery is returning more than one value. How to fix this?

This is a part of my stored procedure. I am having an above mentioned error in this query 

 from Log PA
  left join workflow WF on PA.newWFid =
 (CASE 
 WHEN PA.UserCode is not null THEN (SELECT ExistingwfID from RMShuffleLog RM 
 LEFT JOIN PackageApprovalLog PA on PA.ShuffleId = RM.ShuffleId )
 end)

It was working fine but I added the case expression in join condition above. This is what I added above:

     left join workflow WF on PA.newWFid =
 (CASE 
 WHEN PA.UserCode is not null THEN (SELECT ExistingwfID from RMShuffleLog RM 
 LEFT JOIN PackageApprovalLog PA on PA.ShuffleId = RM.ShuffleId )
 end)

After this, it is showing me error. 

      Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, 
     <= , >, >= or when the subquery is used as an expression

.My logic is to check if the first row of the user code is null then select existingwfid from RMshufflelog. Can anyobdy tell me what the problem is?

Upvotes: 0

Views: 522

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

This query is being used as a scalar subquery, meaning that you get the specified error if it returns more than one row:

(SELECT ExistingwfID
 FROM RMShuffleLog RM LEFT JOIN
      PackageApprovalLog PA 
      ON PA.ShuffleId = RM.ShuffleId

)

This will occur if RMShuffleLog has more than one row or PackageApprovalLog has more than one matching row.

You have a PA in the outer query. So I am guessing you want a correlated clause:

(SELECT ExistingwfID
 FROM RMShuffleLog RM
 WHERE PA.ShuffleId = RM.ShuffleId
)

This is (informed) speculation. There could be other issues with your query as well.

Upvotes: 1

Related Questions