July 10, 2012 at 1:42 am
I declare SUM variable , then i want to put value into it from below cursor , when i wrote :
(SELECT @sum=@Avamel ) or (set @sum=@avamel) or set @sum=(select @avamel)
i give error , please tel my how i can fill sum with Avamel value ?
-------------------------------------
SET NOCOUNT ON;
Declare @90000119 varchar(8),@sum varchar(50)
Declare @cslPricingFactorRef varchar(3),@Avamel varchar(50)
SET @90000119 = 0;@Sum=0
Declare spid_List2 Cursor FORWARD_ONLY For
SELECT cslPricingFactorRef,Avamel
from V_Factor_Total4
Open spid_List2
Fetch Next From spid_List2 into @cslPricingFactorRef,@Avamel
While @@Fetch_Status = 0
Begin
select
CASE
WHEN (@cslPricingFactorRef= 90000119) THEN (SELECT @sum=@Avamel)
END
Fetch Next From spid_List2 into @cslPricingFactorRef,@Avamel
End
Close spid_List2
Deallocate spid_List2
July 10, 2012 at 3:12 am
Surely this is sufficient:
SELECT SUM(Avamel)
FROM V_Factor_Total4
WHERE cslPricingFactorRef = '90000119'
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
July 10, 2012 at 3:50 am
i know it , but i need it value in cursor
July 10, 2012 at 3:59 am
eh.shams (7/10/2012)
i know it , but i need it value in cursor
Why do you need a cursor? Surely you just need the value in a variable?
SELECT @Variable = column FROM ...
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
July 10, 2012 at 4:20 am
i write :
SELECT @90000119Sum = @Avamel
"@90000119Sum" this is variable i declare it,@Avamel is belong to cursor as shown in top
in cursor but give me this error :
Incorrect syntax near the keyword 'SELECT'.
July 10, 2012 at 4:45 am
eh.shams (7/10/2012)
i write :SELECT @90000119Sum = @Avamel
"@90000119Sum" this is variable i declare it,@Avamel is belong to cursor as shown in top
in cursor but give me this error :
Incorrect syntax near the keyword 'SELECT'.
Why do you need a cursor for this?
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
July 10, 2012 at 4:54 am
because i need to analysis each row of cursor , and make decision by value of column
July 10, 2012 at 4:56 am
eh.shams (7/10/2012)
because i need to analysis each row of cursor , and make decision by value of column
Like this:
CASE
WHEN (@cslPricingFactorRef= 90000119) THEN (SELECT @sum=@Avamel)
What are the other decisions?
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
July 10, 2012 at 5:08 am
i try :
While @@Fetch_Status = 0
Begin
select
CASE
WHEN (@ProductCode = 90000119) and (@cslPricingFactorRef = 95) THEN (SELECT @90000119Sum = @Avamel)
END
Fetch Next From spid_List2 into @cslPricingFactorRef,@Avamel,@Price,@Quantity,@UnitPrice,@ProductCode,@ProductName
End
but give this error :
Incorrect syntax near '='.
July 10, 2012 at 5:20 am
eh.shams (7/10/2012)
i try :While @@Fetch_Status = 0
Begin
select
CASE
WHEN (@ProductCode = 90000119) and (@cslPricingFactorRef = 95) THEN (SELECT @90000119Sum = @Avamel)
END
Fetch Next From spid_List2 into @cslPricingFactorRef,@Avamel,@Price,@Quantity,@UnitPrice,@ProductCode,@ProductName
End
but give this error :
Incorrect syntax near '='.
I am sure you don't need a cursor for this, it's a simple aggregate. Try this:
SELECT @90000119Sum = SUM(Avamel)
FROM V_Factor_Total4
WHERE cslPricingFactorRef = '95'
AND ProductCode = '90000119'
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
July 10, 2012 at 5:28 am
it is not agggregation, the name of my variable is (90000120sum)
July 10, 2012 at 5:31 am
eh.shams (7/10/2012)
it is not agggregation, the name of my variable is (90000120sum)
SELECT @90000119Sum = Avamel
FROM V_Factor_Total4
WHERE cslPricingFactorRef = '95'
AND ProductCode = '90000119'
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
July 10, 2012 at 5:32 am
please attention , these are variable not aggregation function, i chenge it name to ali
SET NOCOUNT ON;
Declare @90000119 varchar(8),@ali varchar(50)
Declare @cslPricingFactorRef varchar(3),@Avamel varchar(50)
SET @90000119 = 0;@ali=0
Declare spid_List2 Cursor FORWARD_ONLY For
SELECT cslPricingFactorRef,Avamel
from V_Factor_Total4
Open spid_List2
Fetch Next From spid_List2 into @cslPricingFactorRef,@Avamel
While @@Fetch_Status = 0
Begin
select
CASE
WHEN (@cslPricingFactorRef= 90000119) THEN (SELECT @ali=@Avamel)
END
Fetch Next From spid_List2 into @cslPricingFactorRef,@Avamel
End
Close spid_List2
Deallocate spid_List2
July 10, 2012 at 5:42 am
There's no aggregate in my previous post:
SELECT @90000119Sum = Avamel
FROM V_Factor_Total4
WHERE cslPricingFactorRef = '95'
AND ProductCode = '90000119'
Why do you need a cursor for this?
Performance is likely to be very poor, and untunable.
The code will be far more complex than it needs to be, making maintenance much more difficult.
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
July 10, 2012 at 7:30 am
eh.shams (7/10/2012)
please attention , these are variable not aggregation function, i chenge it name to ali
SET NOCOUNT ON;Declare @90000119 varchar(8),@ali varchar(50)
Declare @cslPricingFactorRef varchar(3),@Avamel varchar(50)
SET @90000119 = 0;@ali=0
Declare spid_List2 Cursor FORWARD_ONLY For
SELECT cslPricingFactorRef,Avamel
from V_Factor_Total4
Open spid_List2
Fetch Next From spid_List2 into @cslPricingFactorRef,@Avamel
While @@Fetch_Status = 0
Begin
select
CASE
WHEN (@cslPricingFactorRef= 90000119) THEN (SELECT @ali=@Avamel)
END
Fetch Next From spid_List2 into @cslPricingFactorRef,@Avamel
End
Close spid_List2
Deallocate spid_List2
(added code tags)
ok first as chrisM has suggested if this is all of your cursor you can do it in a select. if this is not all of the code please post the entire piece of code and we can still possibly remove the cursor.
second, i think this may need to be a "," not a ";" (See following snip-it)
SET @90000119 = 0;@ali=0
Third i think you are trying to use @90000119 but are forgetting the "@", so you end up comparing @cslPricingFactorRef to the INT '90000119' (See following snip-it)
WHEN (@cslPricingFactorRef= 90000119) THEN (SELECT @ali=@Avamel)
judging from the fact you never actually display the variables or do any thing with them other than the case statement i am guessing this is not all of the code. For the best possible answer please post all the code and DDL and Sample Data. See the first link in my signature for the best practices on posting questions to the forum.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply