January 5, 2009 at 3:44 pm
Here is my code. When I print the update statement out I can copy and execute with no problem. However, when running within the cursor the Where clause never returns true. Any ideas?
DECLARE VarianceCursor Cursor For
Select Product, Variance From RateVariance Where Active = '1'
OPEN VarianceCursor
Declare @product varchar(25)
Declare @Variance decimal(18,6)
Fetch Next From VarianceCursor INTO @product, @Variance
While @@Fetch_Status = 0
Begin
Update CustomersProducts
Set RateVariance = RateVariance + @Variance
WHERE @product = 'Yes'
Fetch Next From VarianceCursor INTO @product, @Variance
End
Close VarianceCursor
Deallocate VarianceCursor
January 5, 2009 at 4:01 pm
You do understand that your Update statement as provided will Update all CustomerProduct rows whenever the@Product variable is "YES"?
I'd do this without a cursor:
Update CP
Set RateVariance = RateVariance + RV.Variance
From
CustomerProducts CP Join
RateVariance RV ON
CP.Product = RV.Product
Where
RV.Active = '1'
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 5, 2009 at 4:25 pm
Thank you for the reply but the update statement will not work because you cannot join the customersproducts table to the ratevariance table. Each Record in the RateVariance is set using the @product. So for each row in the rate variance table I want to run an update on the column in the customersproducts table that corresponds to the @product Variable if the @product field = Yes
January 5, 2009 at 4:30 pm
Based on the limited information you have provided I don't know what help you can get. Please see the links in my signature about how to post questions so you have the best chance of getting an accurate answer. DDL is a must in this case.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 5, 2009 at 4:57 pm
I have to agree with Jack - this really does not make sense. The cursor is defined to select two values (Product and Variance).
You then create a cursor over the results of that statement. Based upon the code in the update statement, you are expecting a value in the variable @product (from the RateVariance table) to be 'YES'.
If you have a row in the RateVariance table that is active, with a product = 'YES' - then you take the rate variance from that row and add it to every single CustomerProducts row.
This really does not sound right to me.
Please read the post in my signature (or Jack's - it is the same article) and post the table definitions, sample data and expected results.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 5, 2009 at 5:54 pm
As yoda would say 'sense your sql does make not - post table def and data you must. expected results. hmmmmmm'
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
January 5, 2009 at 6:04 pm
Basically here is the table structure for each table:
RateVariance Table
Description Variance Active
Loan .005 1
Insurance .004 1
Customers Products Table
CustomerID Loan Insurance RateVariance
123 Yes No 0
789 No Yes 0
178 Yes Yes 0
The cursor currently would produce the following sql based on the info above:
Update CP
Set RV = RV + .005
Where Loan = 'Yes'
Update CP
Set RV = RV + .004
Where Insurance = 'Yes'
The Rate Variance in the customersproducts table will always start at 0. Then if they have a loan then the variance would increase by .005?
The cursor currently writes out the correct sql statements when I print them out it just does not execute the Where clause accurately. The
January 5, 2009 at 6:12 pm
declare @sql varchar(4096)
set @sql = ''
select @sql = @sql + 'UPDATE [CustomerProducts] SET [RateVariance] = [RateVariance] + ' +
convert(varchar, [Variance]) + ' WHERE [' + Description + '] = ''Yes'';'
from [RateVariance] WITH (NOLOCK)
where active = 1
exec (@sql)
But really, that schema makes me sad inside.
edit -> formatted less wide, added where active = 1
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
January 6, 2009 at 12:26 am
Matt Whitfield (1/5/2009)
declare @sql varchar(4096)
set @sql = ''
select @sql = @sql + 'UPDATE [CustomerProducts] SET [RateVariance] = [RateVariance] + ' +
convert(varchar, [Variance]) + ' WHERE [' + Description + '] = ''Yes'';'
from [RateVariance] WITH (NOLOCK)
where active = 1
exec (@sql)
Yes this is a the basic idea behind dynamic SQL. How many "types" of products do you have and are there going to be more added later? If they are static and few I still think you are better off with a set-based update, I am also assuming that there will only be 1 active RateVariance row for each product type, like this (for future reference note how I provided test tables and data so anyone can test the code):
-- create test tables
Declare @RateVariance Table (Description varchar(10), Variance decimal(6, 3), active bit);
Declare @CustomerProducts Table (CustomerId Int, Loan varchar(3), Insurance varchar(3), RateVariance decimal(6, 3));
-- insert test data into tables
Insert Into @RateVariance
Select
'Loan',
0.005,
1
Union All
Select
'Insurance',
0.004,
1;
Insert @CustomerProducts
Select
123,
'Yes',
'No',
0
Union All
Select
124,
'No',
'Yes',
0
Union All
Select
125,
'Yes',
'No',
0
Union All
Select
126,
'Yes',
'Yes',
0;
-- before the updates
Select * From @CustomerProducts
-- update loans
Update @CustomerProducts
Set RateVariance = RateVariance + (Select Variance
from @RateVariance
Where Description = 'Loan' and active = 1)
Where
Loan = 'Yes';
-- update insurances
Update @CustomerProducts
Set RateVariance = RateVariance + (Select Variance
from @RateVariance
Where Description = 'Insurance' and active = 1)
Where
Insurance = 'Yes';
-- data after the update
Select * From @CustomerProducts;
But really, that schema makes me sad inside.
I agree. A better design would have a product_type in both tables and you could join on that.
-- EDIT - made last row inserted into @CustomerProducts table a Yes - Yes so both Rate Variances would be applied which I believe is the desired behavior.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 6, 2009 at 3:13 am
Erm... that version doesn't actually do what's required does it? What happens if a new type other than 'Loan' or 'Insurance' is added?
Not that the schema makes that a change-free scenario! 😛
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
January 6, 2009 at 6:39 am
Matt Whitfield (1/6/2009)
Erm... that version doesn't actually do what's required does it? What happens if a new type other than 'Loan' or 'Insurance' is added?Not that the schema makes that a change-free scenario! 😛
Which is why I prefaced the code with:
How many "types" of products do you have and are there going to be more added later? If they are static and few I still think you are better off with a set-based update, I am also assuming that there will only be 1 active RateVariance row for each product type
They are in a world of hurt if they add anything, assuming this is a DB already in production, if not they have time to fix it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 6, 2009 at 6:43 am
Ahh I get what you were saying now. Sorry read it differently the first time. It's just where you had the code posted up that required a manually written statement for each new row, presumably the original poster could have done that - but I just looked at the code and not what you'd written - my bad! :hehe:
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
January 7, 2009 at 11:44 am
You've probably already overcome this, but...
Since the value 'loan' is actually the name od a column, you'll need to build the name into a string to execute, vis:
DECLARE VarianceCursor Cursor For
Select Product, Variance From RateVariance Where Active = '1'
OPEN VarianceCursor
Declare @product varchar(25)
Declare @Variance decimal(18,6)
Fetch Next From VarianceCursor INTO @product, @Variance
While @@Fetch_Status = 0
Begin
EXEC ('
Update CustomersProducts
Set RateVariance = RateVariance + ' + @Variance + '
WHERE ' + @product + ' = ''Yes''')
Fetch Next From VarianceCursor INTO @product, @Variance
End
Close VarianceCursor
Deallocate VarianceCursor
Mark
Just a cog in the wheel.
January 7, 2009 at 1:26 pm
Joe Celko (1/7/2009)
"A problem well stated is a problem half solved." -- Charles F. KetteringPlease post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html
What you did post was full of magical, universal "id" that changed meaning from table to table -- not even close the RDBM datga modeling. Let me make a guess and try to correct what you did notm bother to post.
Also, in 25+ years of writing SQL code and Language Standards, I have used five cursors. Three of them would have been avoided with CASE expressions and the other two should not have been done in SQL at all (NP-complete problems). My guess based on my experience is that you are doing it all wrong and don't need or want a cursor at all.
Thanks for the note, Joe.
I was just trying to get the original poster out of a jam, not help him re-invent his database. Sometimes we just need to get something done and move on.
Cheers from Denver,
Mark
Mark
Just a cog in the wheel.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply