December 21, 2012 at 7:29 am
Hi, I'm new to SQL stuff but so far am managing to get my head around most things. :w00t:
I have a database that has pricing info in it for customers. Essentially, there is a customer entry in the table for each of our product groups together with a discount for that group. The entries are listed similar to that shown below:
CUSTOMER_NAME, PRODUCT_GROUP, DISCOUNT
The entries are all single-line in the table so the customer name is repeated many times as the table contains a list in the format above for each customer. Odd way to do it but that's the way our system tables are set up.
I wrote a routine to ADD another group for each customer (that worked fine, now I have the new "discount empty" rows in the table) but I need to take the DISCOUNT field from another group (SUPPORT) in my example and copy the (SUPPORT) discount for each customer into the new group (HARTING). I'm declaring variables and a method to do this but not only does it not work but it throws an error as below:
HRESULT E_FAIL call to COM component
I'm not sure if my code is rubbish or this is a server error. Could someone please take a look at the code and tell me if I got it completely wrong? SQL Query is like this:
/* SQL Routine to copy discount percentage from one group to another */
declare @code1 VARCHAR(30)
declare @code2 VARCHAR(30)
declare @code3 DECIMAL
declare @code4 DECIMAL
declare get_matrix CURSOR
FOR SELECT mat_customer, mat_stock_key, mat_percent1
/* Select Group to copy from */
FROM stk_matrix WHERE mat_customer <> '' AND mat_stock_key = 'SUPPORT'
/* Open Macro */
OPEN get_matrix
FETCH get_matrix INTO @code1, @code2, @code3
WHILE @@fetch_status = 0
IF @code2 = 'SUPPORT'
/* Move Support Discount to new variable */
SET @code4 = @code3
/* Now process data collected */
BEGIN
UPDATE stk_matrix
SET mat_percent1 = @code3 WHERE mat_customer = @code1
and mat_stock_key = 'HARTING'
PRINT ' Stock Code: ' + ' Added ' + @code1 + '%' + ' to Customer ' + @code1 + ' HARTING'
SET @code4 = '0.00'
/* Next */
FETCH get_matrix INTO @code1, @code2, @code3
END
FETCH get_matrix INTO @code1, @code2, @code3
CLOSE get_matrix
DEALLOCATE get_matrix
>>>>>>>>>>>>>
I think my error is on the "FROM stk_matrix........" line but maybe not. I'm trying to test all table lines for "Customer" and a match to "Support" - then retrieve the value of the "Discount" in the SUPPORT Discount field and poke it into a variable then update the table with this value which matches the CUSTOMER and the new "HARTING" row.
I've used code similar to the above and it worked fine before but it was doing something slightly different. I think the problem is that my understanding of the way this works is flawed.
Can anyone point me in the right direction? Sorry for the long post!
Thanks.
December 21, 2012 at 8:00 am
I don't use cursors myself but...
Surely you need a BEGIN -- END block for the WHILE?
Have you inserted the "IF @code2 = 'SUPPORT'
/* Move Support Discount to new variable */
SET @code4 = @code3" code & separated the BEGIN - END block from the WHILE??
declare @code1 VARCHAR(30)
declare @code2 VARCHAR(30)
declare @code3 DECIMAL
declare @code4 DECIMAL
declare get_matrix CURSOR
FOR SELECT mat_customer, mat_stock_key, mat_percent1
/* Select Group to copy from */
FROM stk_matrix WHERE mat_customer <> '' AND mat_stock_key = 'SUPPORT'
/* Open Macro */
OPEN get_matrix
FETCH get_matrix INTO @code1, @code2, @code3
WHILE @@fetch_status = 0
BEGIN
IF @code2 = 'SUPPORT'
/* Move Support Discount to new variable */
SET @code4 = @code3
/* Now process data collected */
BEGIN
UPDATE stk_matrix
SET mat_percent1 = @code3 WHERE mat_customer = @code1
and mat_stock_key = 'HARTING'
PRINT ' Stock Code: ' + ' Added ' + @code1 + '%' + ' to Customer ' + @code1 + ' HARTING'
SET @code4 = '0.00'
/* Next */
FETCH get_matrix INTO @code1, @code2, @code3
END
END
FETCH get_matrix INTO @code1, @code2, @code3 -- Do you need this?
CLOSE get_matrix
DEALLOCATE get_matrix
December 21, 2012 at 8:12 am
This might be a better way of doing it, if I've understood the requirement:
--== TEST DATA ==
-- Don't run this on your real table!!!
--IF OBJECT_ID('stk_matrix') IS NOT NULL
--DROP TABLE stk_matrix;
CREATE TABLE stk_matrix
(
mat_customer varchar(10),
mat_stock_key varchar(10),
mat_percent1 decimal(7,3)
);
insert stk_matrix values ( 'XXX', 'HARTING', 0 );
insert stk_matrix values ( 'XXX', 'SUPPORT', 1.77 );
insert stk_matrix values ( 'YYY', 'HARTING', 0 );
insert stk_matrix values ( 'YYY', 'SUPPORT', 3.33 );
SELECT * FROM stk_matrix;
--== SUGGESTED SOLUTION ==
WITH CTE AS
(
SELECT mat_customer, mat_stock_key, mat_percent1
FROM stk_matrix WHERE mat_customer <> '' AND mat_stock_key = 'SUPPORT'
)
UPDATE stk_matrix
SET mat_percent1 = CTE.mat_percent1
FROM stk_matrix SM
INNER JOIN CTE ON CTE.mat_customer = SM.mat_customer
and SM.mat_stock_key = 'HARTING'
SELECT * FROM stk_matrix;
December 21, 2012 at 9:29 am
The point that Laurie is making is that cursors are absolutely awful for performance. There are a few times when they are needed (maintenance scripts) but your example is not one of those times. Doing this type of thing in a cursor will be slow now and progressively get slower as the amount of data increases. The way to do this is a single update statement like Laurie suggested. If you are unable to figure out the syntax to work with your system and need additional help please see the first link in my signature for best practices when posting questions.
I would highly recommend you get rid of all the cursors in this process and any other process that is similar. That path to accomplishing this is that you have to change your mindset about data. Think about what you want to do to a column instead of what you want to do to a row.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2012 at 10:32 am
Great suggestions guys.
Thank you. Will be trying a few and other methods to achieve this.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply