August 25, 2013 at 6:47 am
Once I run this query, I get the following error.
DECLARE@Type int,@SearchStr2 nvarchar(200)
SET @SearchStr2 = 'A'
SELECT * FROM Document WHERE DocNo in(
CASE @Type
WHEN 1 THEN
(SELECT DocNO FROM Publisher WHERE CONTAINS((PublisherName), @SearchStr2)
)
WHEN 2 THEN
(SELECT DocNO FROM Publisher WHERE CONTAINS((PublishedPlace), @SearchStr2)
)
WHEN 3 THEN
(SELECT DocNO FROM Publisher WHERE CONTAINS((PublishedDate), @SearchStr2)
)
END
)
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.
I Find that CASE @Type WHEN ... END needs to evaluate to one value but My sub queries aren't doing that.
Now,How can I correct the query?
August 25, 2013 at 10:09 am
Presumably you are using IN rather than = because you want all the documents that satisfy the condition in the cases where there are more than one. But CASE can only return a single scalar value, so the result expression in each branch needs a single scalar value, so you can't use CASE to return a set. However, the particular error message you are getting is caused by the brackets round each of the three subqueries: putting the subquery in brackets requires it to deliver a single row containing a single value, and there is more than one PublisherName in you Publisher full text indexed view or table that contains the letter 'A'.
If you don't want to cope with multiple matches (which seems unlikely) you could try changing IN to = , deleting the brackets round CASE....END, leaving the three subqueries in brackets and changing the three subqueries to have "TOP 1" after "SELECT". But I am pretty sure that will not meet your requirement.
A rather more likely solution is to write it using IF instead of CASE:-
IF @Type = 1 THEN SELECT DocNO FROM Publisher WHERE CONTAINS(PublisherName, @SearchStr2)
ELSE IF @Type = 2 THEN SELECT DocNO FROM Publisher WHERE CONTAINS(PublishedPlace, @SearchStr2)
ELSE IF @Type = 3 THEN SELECT DocNO FROM Publisher WHERE CONTAINS(PublishedDate, @SearchStr2) ;
Tom
August 25, 2013 at 10:28 am
Try pushing the CASE expression into the CONTAINS function.
... IN (
select docno from ... Where contains(case when @type ... End, @var)
);
Remember that the type of those columns should be compatible otherwise you will have to convert them explicitly or you will get an error.
August 25, 2013 at 3:58 pm
SELECT *
FROM Document D
WHERE (@Type = 1 AND
EXISTS (SELECT *
FROM Publisher P
WHERE P.DocNo = D.DocNo)
AND CONTAINS(P.PublisherName, @SearchStr2) OR
@Type = 2 AND
EXISTS (SELECT *
FROM Publisher P
WHERE P.DocNo = D.DocNo)
AND CONTAINS(P.PublisherPlace, @SearchStr2) OR
@Type = 3 AND
EXISTS (SELECT *
FROM Publisher P
WHERE P.DocNo = D.DocNo)
AND CONTAINS(P.PublisherDate, @SearchStr2))
The nice thing here is that the optimizer will add a startup expression, only one of the CONTAINS queries will be executed.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 26, 2013 at 7:26 am
Erland Sommarskog (8/25/2013)
The nice thing here is that the optimizer will add a startup expression, only one of the CONTAINS queries will be executed.
But is that just what currently happens but might change in a future release, potentially introducing an unpleasant performance problem, or is it something which is documented as what T-SQL is specified to do and is guaranteed not to change without notice?
Tom
August 26, 2013 at 7:35 am
There is always that the caveat that the optimizer might change.
However, it's a little difficult to see why it would happen in this case. Changes in the optimizer are usually intended to give better performance for many even if it can backfire for some. But what change that would be in this case, I don't see.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply