July 10, 2012 at 1:17 pm
Setting aside why you may or may not need a cursor, what you need to do is:
Either:
select @sum =
CASE
WHEN (@cslPricingFactorRef = @90000119) THEN @Avamel
else
0 end
I don't know what the default should be.
or you could:
If @cslPricingFactorRef = @90000119
SET @sum = @Avamel
Cursors are bad, most times, some times they are necessary.
Try not to use them.
Leonard
July 10, 2012 at 2:26 pm
The cursor aspect has already been discussed at length (i.e. avoid them like the plague).
That said - you're using CASE incorrectly, which is what is causing the error. If you HAVE to have it (pretty sure you don't, but since you're not providing any details as to what you're actually doing I will have to leave it up to you to decide), pull the "assignment" aspect out of the CASE statement.
As in:
select @sum= --assignment goes OUTSIDE of the case
CASE
WHEN (@cslPricingFactorRef= 90000119) THEN @Avamel
End
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 10, 2012 at 11:19 pm
In the select case i have 8 condition,and i make decision on @cslPricingFactorRef value,for example if @cslPricingFactorRef=90000119 then @90000119=@Avamel
if @cslPricingFactorRef=90000120 then @90000120=@Avamel
all of my variable fill in this loop, in cursor
July 10, 2012 at 11:48 pm
Hello , as you mentioned in the select case i can't set the variable with the value of cursor column,
i used this , it work's but need i put more code and if for any product code :
Open spid_List2
Fetch Next From spid_List2 into @cslPricingFactorRef,@Avamel,@Price,@Quantity,@UnitPrice,@ProductCode,@ProductName
While @@Fetch_Status = 0
Begin
--select
--CASE
-- WHEN (@ProductCode = 90000119) and (@cslPricingFactorRef = 95) then @Avamel
-- END
if (@ProductCode = 90000119)and (@cslPricingFactorRef = 95)
begin
set @90000119=@Avamel
print @90000119
end
Fetch Next From spid_List2 into @cslPricingFactorRef,@Avamel,@Price,@Quantity,@UnitPrice,@ProductCode,@ProductName
End
July 11, 2012 at 1:50 am
After you have populated the variables, how do you use their values?
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
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply