case subquery in sql server 2008
the followings statements gives an error
print (case when exists (select count(*) from tblCus开发者_如何学GotomerProductsDiscount PD where PD.cust_ID=138 and PD.pack_detl_ID = 1) then 0 end)
Error: Subqueries are not allowed in this context. Only scalar expressions are allowed.
First of all, while your intention is quite clear, the script in its current form doesn't make sense, and here's why.
You are checking for the existence of rows in the select count(*)...
subselect, but the fact is, COUNT()
always returns a value. In case of no rows for the specified condition it will return 0
, but that would still be a row returned by the subquery, and EXISTS
would evaluate to TRUE
in any case.
To fix it, just replace select count(*)
with select *
.
Another thing is the error. Subqueries are not allowed in this context, and that is final. With PRINT
you cannot use a subquery in any form. Store the result in a variable and PRINT
the variable:
declare @result int;
set @result = case
when exists (
select *
from tblCustomerProductsDiscount PD
where PD.cust_ID=138 and PD.pack_detl_ID = 1
)
then 0
end
print @result;
精彩评论