March 5, 2008 at 10:18 am
Hi,
I have had bad experiences with CURSORS and do not know if something like what I have below does the same thing? Can UPDATE update multiple records without a cursor and update with different data for each record?
I have a table (z_Update2) containing data destined for a second table (Locations_test). The only way to connect the two tables is through a third table (CoInformation). Loc_No is in z_Update2 and CoInformatio and fkProjectID is in CoInformation and Locations_test.
--update Locations_test Division 0 records with
--Division 0 addr data (a_) in z_Update2
UPDATE Locations_test
SET LocationName = u.a_ShipName,
Address1 = u.a_ShipAddr1,
Address2 = u.a_ShipAddr2,
City = u.a_ShipCity,
State = u.a_ShipState,
ZipCode = u.a_ShipZip,
PhoneNumber = u.a_Phone1
FROM z_Update2 u
WHERE EXISTS
(
SELECT
*
FROM z_Update2 u2
INNER JOIN CoInformation c
ON convert(varchar(20),u2.Loc_No)=c.ClientNumber
INNER JOIN Locations_test r
ON c.fkProjectID=r.fkProjectID
WHERE r.DivNo=0
AND u2.Div_No=0
)
--always create missing Division 0 records with
INSERT INTO Locations_test
(
DivNo,
LocationName,
Address1,
Address2,
City,
State,
Zip,
PhoneNumber
)
SELECT 0,
u.a_ShipName,
u.a_ShipAddr1,
u.a_ShipAddr2,
u.a_ShipCity,
u.a_ShipState,
u.a_ShipZip,
u.a_Phone1
FROM z_Update2 u
INNER JOIN CoInformation c
ON c.ClientNumber=convert(varchar(20),u.Loc_No)
WHERE NOT EXISTS
(
SELECT *
FROM Locations_test r
INNER JOIN CoInformation c
ON r.fkProjectID=c.fkProjectID
WHERE c.ClientNumber=convert(varchar(50), u.Loc_No)
AND u.Div_No=0
)
Warm regards,
March 5, 2008 at 10:29 am
with sqlserver you can update one alias of a join directly.
e.g.
update T2
set colx = T1.coly
from mytableA T1
inner join mytableB T2
on T2.thekey = T1.TheMatchingKey
inner join someothertable T3
on T1.anothercol = T3.matchingcol
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 5, 2008 at 12:14 pm
Hi and thank you ALZDBA,
So that's what I kinda figure - one match and I'm done
Ok, it will be written with cursors then.
NEW QUESTION:
One question regarding CURSORS, in the line where I do 'if exists (@ClientNumber... is that legal to then use @ClientNumber in the WHERE clause of the UPDATE?
WHILE @@FETCH_STATUS = 0
BEGIN
-- if @Div_No = 0 we need to insert or update from the a_ data
-- else we need to insert or update from the d_ data
if (@Div_No = 0)
begin
if exists ( @ClientNumber = SELECT c.ClientNumber r.fkProjectID
FROM Locations_test r
INNER JOIN CoInformation c
ON r.fkProjectID=c.fkProjectID
WHERE c.ClientNumber=convert(varchar(20),@Loc_No)
AND r.DivNo=0
)
update Locations_test set
r.LocationName = @a_ShipName,
r.Address1 = @a_ShipAddr1,
r.Address2 = @a_ShipAddr2,
r.City = @a_ShipCity,
r.State = @a_ShipState,
r.ZipCode = @a_ShipZip,
r.PhoneNumber = @a_Phone1
where @ClientNumber=convert(varchar(20),@Loc_No) AND r.DivNo=@Div_No)
else
insert into Locations_test
(
Div_No,
LocationName,
Address1,
Address2,
City,
State,
ZipCode,
PhoneNumber
)
values
(
0,
@a_ShipName,
@a_ShipAddr1,
@a_ShipAddr2,
@a_ShipCity,
@a_ShipState,
@a_ShipZip,
@a_Phone1
)
fetch next from cursor_03 into
@Loc_No,
@Div_No,
@a_ShipName,
@a_ShipAddr1,
@a_ShipAddr2,
@a_ShipCity,
@a_ShipState,
@a_ShipZip,
@a_Phone1,
@UsingDivisions,
@NextSchedDelDate,
@PayDate,
@CallInDay,
@d_ShipName,
@d_ShipAddr1,
@d_ShipAddr2,
@d_ShipCity,
@d_ShipState,
@d_ShipZip,
@d_County
end
-- else @Div_No <> 0 and we need to insert or update from the d_ data
else
begin
if exists ( @ClientNumber = SELECT c.ClientNumber
FROM Locations_test r
INNER JOIN CoInformation c
ON r.fkProjectID=c.fkProjectID
WHERE c.ClientNumber=convert(varchar(20),@Loc_No)
AND r.DivNo=@Div_No
)
update Locations_test set
r.LocationName = @d_ShipName,
r.Address1 = @d_ShipAddr1,
r.Address2 = @d_ShipAddr2,
r.City = @d_ShipCity,
r.State = @d_ShipState,
r.ZipCode = @d_ShipZip,
r.PhoneNumber = @d_Phone1
where @ClientNumber=convert(varchar(20),@Loc_No) AND r.DivNo=@Div_No)
else
insert into Locations_test
(
Div_No,
LocationName,
Address1,
Address2,
City,
State,
ZipCode,
PhoneNumber
)
values
(
0,
@a_ShipName,
@a_ShipAddr1,
@a_ShipAddr2,
@a_ShipCity,
@a_ShipState,
@a_ShipZip,
@a_Phone1
)
fetch next from cursor_03 into
@Loc_No,
@Div_No,
@a_ShipName,
@a_ShipAddr1,
@a_ShipAddr2,
@a_ShipCity,
@a_ShipState,
@a_ShipZip,
@a_Phone1,
@UsingDivisions,
@NextSchedDelDate,
@PayDate,
@CallInDay,
@d_ShipName,
@d_ShipAddr1,
@d_ShipAddr2,
@d_ShipCity,
@d_ShipState,
@d_ShipZip,
@d_County
end
March 6, 2008 at 7:11 am
I think I didn't express myself well.
My suggestion was nothing else but a starting point !
Why would you need a cursor ?
If you need to do quit exact the same thing with your data,
don't use a cursor !
Write a select statement to just select all involved data
Then replace the select by update youralias ,
set the correct columns to the new values, and your done !
Keep in mind, this is a set based operation, meaning, if you have table scans involved, you may get some locking issues !
(this is something you'll have to experience before switching to less optimal solutions)
In your case, just take the select you used in your cursor definition,
join it to the update statement, and you're quit done.
if you have optional columns for update, use a case statement !
Keep in mind to always put the original column (from the update-table) in the else branch or your update result for that column will be NULL :crazy:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 7, 2008 at 6:43 pm
In general, I would say DO NOT use a cursor WHEN a set operation can do the job.
The controversy about cursors stems, in part, from the bad habits some programmers accustomed with a record-based database, instead of a set-based database. Writing a loop to update 10 records, updating each one after the other and then moving on to the next record.
HOWEVER there are cases where a set operation cannot work:
An example of when CURSORS can be useful:
Let's say a customer is entitled to the following discounts:
-- 6 % general discount applied on the gross sales amount (6 % * $1, 000.00) = $60.00
-- 3 % discount for slotting allowance applied AFTER the general discount is applied
-- i.e. 3 % * ($1,000.00 - $ 60,00) = 3 % * $ 940 = $ 28.20
-- 2 % discount for volume rebate after the 3 % allowance has been applied
-- i.e. 2 % * ($ 940.00 - $ 28.20) = 2 % * $ 911.80 = $ 18.24
Since the last two steps depends on a the previous step, there is no way to do with a single SET operation, since the definition of set does not include the concept of precedence between set members.
Yes you write multiple UPDATE statements. But say you have 10 discounts to apply.
I definitely prefer using a CURSOR rather than code 10 UPDATE statements.
Or you want to issue a consecutive number to each record in a table, based on certain conditions.
There are cases where a
WHILE @Rec_No < @Rec_Count BEGIN
... some statement
SET @Rec_No = @Rec_No + 1
END
March 9, 2008 at 6:19 pm
J, your example does not require cursor either.
Let's assume you have a table holding all your discounts:
[Code]
CREATE TABLE dbo.Temp_Discount (
OrderID int NOT NULL PRIMARY KEY,
Amount FLOAT NOT NULL
)
INSERT INTO dbo.Temp_Discount (
OrderID, Amount)
SELECT 1, 0.06
UNION
SELECT 2, 0.03
UNION
SELECT 3, 0.02
---------------------------------------------
[/Code]
Now create the function doing all calculations for you:
[Code]
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'DiscountedAmount')
DROP FUNCTION DiscountedAmount
GO
CREATE FUNCTION dbo.DiscountedAmount
(@SaleAmount money)
RETURNS money
AS
BEGIN
SELECT @SaleAmount = @SaleAmount * (1-Amount)
FROM dbo.Temp_Discount
ORDER BY OrderID
RETURN @SaleAmount
END
GO
[/Code]
Now try it:
[Code]
SELECT dbo.DiscountedAmount (1000)
--Or for an actual table:
SELECT Amount, dbo.DiscountedAmount (Amount)
FROM dbo.Sales
[/Code]
_____________
Code for TallyGenerator
March 10, 2008 at 2:08 am
Sergiy (3/9/2008)
SELECT @SaleAmount = @SaleAmount * (1-Amount)FROM dbo.Temp_Discount
ORDER BY OrderID
There is a very nice must read article bij Jeff Moden regarding this construct.
http://qa.sqlservercentral.com/articles/Advanced+Querying/61716/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2008 at 7:02 am
Sergiy,
Thank you for your post. This kind of exchange of ideas is in good part what makes this forum great.
I really enjoy being offered constructive criticism on my posts, this is almost like being in class asking my questions to people who know more than I do and are willing to share their knowledge.
One question though: is invoking a FUNCTION for each and every record (up to 300 in the set) really better - performance-wise - than using cursors? And is it possible that SQL Server engine uses cursors internally?
Again, I do not view cursors either as the universal panacea anymore than I view them as an abomination. I do think cursors can be appropriate in some cases.
In the meantime, I'll go read ALZDBA's reference to Jeff Moden's article on this subject (Mr. Moden's prolific articles are always welcome too).
Again, thanks again Sergiy.
March 10, 2008 at 8:12 am
ALZDBA (3/10/2008)
Sergiy (3/9/2008)
SELECT @SaleAmount = @SaleAmount * (1-Amount)FROM dbo.Temp_Discount
ORDER BY OrderID
There is a very nice must read article bij Jeff Moden regarding this construct.
http://qa.sqlservercentral.com/articles/Advanced+Querying/61716/
Speaking of the "running aggregate" method - I would probably extend Sergiy's thought as to an "applicable discounts" table by customer, so that we could calculate the "running discount" on all available discounts by customer (since applying multiple discounts is transitive). Once you have the running discount calculated - you just want to pull out the min of the running percentage and apply that to the customer amount. (Sergiy's function as it now stands requires a "one size fits all" model on the discounts).
It will quite literally blow the doors off of any cursor operation on any production-sized dataset. (and I would wager - the function as well).
----------------------------------------------------------------------------------
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?
March 10, 2008 at 8:37 am
Matt,
If I read you correctly, I should compute a weighted combination of the various discount rates beforehand and just apply a wholesale update to each sale.
There are some discounts that must be shown on the invoice and others that are accumulated and paid by cheque every three months. Also, for a store opening, an extra discount is applied one time only. There may be 300 stores combined a single invoice and very few of them would qualify for the store opening discount.
I supposed I could compute different weighted combined discounts and for each store apply the relevant one.
But then remains the issue of roundoff errors. I am using the currency type because the application is in Delphi 6 and its ADO support does not include the numeric or decimal types. The customer will only look at the gross sales amount and will expect that the n % effective discount match against the net sales amount. And I also still need the intermediate discount amounts to justify the accumulated discounts, which must also match.
Regards
March 10, 2008 at 8:37 am
Cursors may be evil, but they are sometimes a necessary evil. I have seen where a cursor based solution out performed a set based solution. Having learned more about SQL over the years, I would like to have the opportunity to rework that particular solution, but unfortunately (but happily), I no longer work for that employer.
I agree, the problem with cursors comes from programmers that come from a record oriented environment applying what the know where a set based solution may be much better.
The real thing is looking at each instance and determining the best solution. Sometimes writing a cursor based solution helps identify what needs to be done and can help with identifying a set based solution. I know that has helped me on a few occasions.
😎
March 10, 2008 at 8:47 am
I guess the question comes down to laying out all of the business rules. The rounding is something that needs to be handled correctly, but if it is - the operations are entirely transitive. If you'd prefer them applied in a specific way - then that too becomes part of the "rules". How you want things rounded/truncated also.
Bottom line though - there's no reason why the discounts would need to be "recalculated" on an invoice by invoice basis, unless the invoice itself is changed. This is one circumstance where I would store the relevant discounting lineitems on either an invoice, or an invoice lineitem level. You wouldn't want an issue where you "can't" recreate invoices because you changed the discount model. Nor does the discount change from month to month if someone doesn't pay it. The results of the "discounts" need to be fixed and stored at the point where you're generating the invoice.
It's going to be difficult to give you enough detail for you to "see" this without a rather complete description of your process and what discounts might apply.
----------------------------------------------------------------------------------
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?
March 10, 2008 at 8:51 am
Also - one parting thought: how to lay out the printed invoice (to show them the "right" amount) is altogether a different "issue" than calculating the invoice amount for the DB. Reports have altogether abilities (like - running amounts, etc...) that can be leveraged, that don't usually apply in queries.
----------------------------------------------------------------------------------
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?
March 10, 2008 at 8:57 am
If discounts have to be applied in a specific order, it does add to the complexity. Ten percent off of ten percent off, is not twenty percent off:
x = 100.00
.9x = 90.00
.9x = 81.00
x = 100.00
.8x = 80.00
This was a significant issue at my last employer.
😎
March 10, 2008 at 9:04 am
Lynn Pettis (3/10/2008)
If discounts have to be applied in a specific order, it does add to the complexity. Ten percent off of ten percent off, is not twenty percent off:x = 100.00
.9x = 90.00
.9x = 81.00
x = 100.00
.8x = 80.00
This was a significant issue at my last employer.
😎
It's a matter of how you "envision" the discount.
6% off = .94
3% off = .97
2% off = .98
No matter what order you apply those in - you will end up with a .94*.97*.98=.893564 discount. Now - if your rule is to truncate the result to 2 digits (absolutely incorrect on the math and fin definitions by the way), then yes - you might introduce some rounding "noise". If you actually use the math correctly - no issue whatsoever.
----------------------------------------------------------------------------------
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?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply