Cursor within DTS

Question

Cursor within DTS

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

LadyG (7/22/2010)
The problem is with the last part of the script, insert & select. Obvioulsy, as new_cost doesn't exist in the sales table, the calculated data needs to go somewhere in order to extract into the log table. Or am I completely on the wrong train of thought?

Without a sample data set to test against, I could only offer a guess.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

LadyG Say Hey Kid Points: 675 More actions · Answer 2

Sent via PM. Would appreciate your ideas. Thanks

LadyG Say Hey Kid Points: 675 More actions · Answer 3

Would appreciate your input to this.

Script doesn't quite work. I'm not getting the values expects, getting 0s and nulls. What is wrong?? Have attached attributes for both tables.

SET NOCOUNT ON

DECLARE @income money,

@cost money,

@costAdd money,

@order_id bigint,

@order_value bigint,

@order_line_no bigint,

@promoID int,

@PromoCheck int,

@entCode bigint,

@new_cost money

UPDATE tmp_hhrsales2 SET

@costAdd = CASE

WHEN net_income = 0 and cost <> 0 THEN @costAdd + @cost

WHEN net_income <> 0 and @costAdd <> 0 THEN 0

ELSE @costAdd END,

cost = CASE

WHEN net_income = 0 and cost <> 0 THEN 0

WHEN net_income <> 0 and @costAdd <> 0 THEN cost + @costAdd

ELSE cost END

-- extra columns for log_hhrsales_cos_change: oldcost and newcost

FROM tmp_hhrsales2 WITH(TABLOCKX)

WHERE PL_Order_Value <> 0 and PromoCheckID <> 60 AND Entity_Code <> 10000001054

OPTION (MAXDOP 1)

INSERT INTO log_hhrsales_cos_change

SELECT PL_Order, PL_Txno, oldcost, new_cost

FROM tmp_hhrsales2

WHERE PL_Order_Value <> 0 and PromoCheckID <> 60 AND Entity_Code <> 10000001054

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

LadyG (7/22/2010)
Sent via PM. Would appreciate your ideas. Thanks

No PM received. Without table creation scripts and some sample data, it's difficult to proceed.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

LadyG Say Hey Kid Points: 675 More actions · Answer 5

LadyG

Say Hey Kid

Points: 675

July 22, 2010 at 8:02 am

#1196865

Sent PM again. HAve you received it??

LadyG Say Hey Kid Points: 675 More actions · Answer 6

LadyG

Say Hey Kid

Points: 675

July 22, 2010 at 9:13 am

#1196923

All attached.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

LadyG (7/22/2010)
Sent PM again. HAve you received it??

Yes thank you, but there is still no script to populate a table with data to use for testing.

Typically it would be something like:

CREATE TABLE #Test (TestID INT, TestStuff VARCHAR(10))

INSERT INTO #Test (TestID, TestStuff)

SELECT 1, 'First test' UNION ALL

SELECT 2, 'Second test' UNION ALL

SELECT 3, 'Third test'

where the column names and values are representative of the problem you are attempting to solve. If you click on the highlighted word this in my signature, it will show you how to do this quickly.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Chris Morris-439714 (7/21/2010)
Even if there is a clustered index on the table in the order that the update is supposed to be executed, there's no guarantee that the rows will be FETCHed in the correct order...

~~You know, of course, that I have to disagree with that if it's a simple update with no cursor.~~ 😉

{EDIT} Ah... sorry... took it out of context.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Jeff Moden (7/22/2010)
Chris Morris-439714 (7/21/2010)
Even if there is a clustered index on the table in the order that the update is supposed to be executed, there's no guarantee that the rows will be FETCHed in the correct order...
~~You know, of course, that I have to disagree with that if it's a simple update with no cursor.~~ 😉
{EDIT} Ah... sorry... took it out of context.

No worries Jeff. I get a warm fuzzy feeling knowing you're peering over my shoulder on this one.

All we need now is a set of sample data to munch.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

LadyG Say Hey Kid Points: 675 More actions · Answer 10

LadyG

Say Hey Kid

Points: 675

July 23, 2010 at 2:45 am

#1197384

Here you go Chris.

Thanks

LadyG Say Hey Kid Points: 675 More actions · Answer 11

LadyG

Say Hey Kid

Points: 675

July 23, 2010 at 2:47 am

#1197385

oops, forgot this one!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

LadyG (7/23/2010)
Here you go Chris.
Thanks

Lovely job LadyG, thanks for this!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

LadyG (7/23/2010)
Here you go Chris.
Thanks

There are a few errors in here - the only one which appears to matter is the CREATE INDEX..statement which references a column which doesn't exist in the table.

The results of the calculation are dependant upon this index.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

LadyG Say Hey Kid Points: 675 More actions · Answer 14

My apologies Chris, the table has a huge host of columns so had to pick out the relevant ones. I have redone the script and data capture as well.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 15

This uses the original data set:

-- change the name of the working table to #_hhrsales2 for testing

-- add a new column ([original_cost] [money] NULL) to the working table

-- There's no work to be done in the original dataset,

-- so create a row to work on

UPDATE #_hhrsales2 SET cost = 0.22

WHERE [PL_Order] = 105553117866341 AND [PL_TxNo] = 13 -- row 6, filtered set, ordered

-- process through the set in the designated order and:-

-- if a row is intercepted where [net_income] = 0 but [cost] <> 0

-- add the [cost] to the variable, and set [cost] to 0

-- if a row is intercepted where [net_income] <> 0 and there's a nonzero value in the variable

-- add the value in the variable to the [cost], and reset the variable to 0.

DECLARE @costSum MONEY, @newcost MONEY

SELECT @costSum = 0, @newcost = 0

UPDATE t SET

original_cost = cost,

@newcost = cost = CASE

WHEN net_income = 0 AND cost <> 0 THEN 0

WHEN net_income <> 0 AND @costSum <> 0 THEN cost + @costSum

ELSE cost END,

@costSum = CASE

WHEN net_income = 0 AND cost <> 0 THEN @costSum + cost

WHEN net_income <> 0 AND @costSum <> 0 THEN 0

ELSE @costSum END

FROM #_hhrsales2 t WITH(TABLOCKX)

WHERE PL_Order_Value <> 0 AND PromoCheckID <> 60 AND Entity_Code <> 10000001054

OPTION (MAXDOP 1)

-- check the results, rows 3 and 4

SELECT *

FROM #_hhrsales2

WHERE [PL_Order] = 105553117866341

ORDER BY [PL_Order], [PL_TxNo] DESC

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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