Cursor within DTS

  • 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

  • Sent via PM. Would appreciate your ideas. Thanks

  • 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

  • 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

  • Sent PM again. HAve you received it??

  • All attached.

  • 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

  • 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)

  • 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

  • Here you go Chris.

    Thanks

  • oops, forgot this one!

  • 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

  • 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

  • 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.

  • 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

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

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