Professional Web Applications Themes

Tough SQL! - Microsoft SQL / MS SQL Server

David, thanks a lot for your help. Once last question. How can I combine these two statements in one batch? This is how I started asking about cursors. I believe I have to run a loop and read the records sequentially? Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...

  1. #1

    Default Re: Tough SQL!

    David, thanks a lot for your help. Once last question. How can I combine
    these two statements in one batch? This is how I started asking about
    cursors. I believe I have to run a loop and read the records
    sequentially?

    Thanks.



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Yaheya Guest

  2. #2

    Default Re: Tough SQL!

    David why do you have MAX(price) in your query?


    University of California at Merced


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Yaheya Guest

  3. #3

    Default Re: Tough SQL!

    MAX(price) was an assumption that I made based on the sample data you
    posted. I should have pointed this out.

    Your sample included the following two rows:

    ('8908ABB23201', 1, 5)
    ('8908ABB23202', 1, 8)

    Both of which match the following row in OrderLines1 based on the first 10
    digits and the Line_number:

    ('8908ABB23200', 1, 4)

    It therefore becomes a question of which price to update the OrderLines1 row
    with. You could use MIN() or MAX(), or did you wanted the one with the
    highest or lowest Line_number? You tell me.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  4. #4

    Default Re: Tough SQL!

    > How can I combine 

    If by "batch" you mean "transaction", like this:

    BEGIN TRAN

    UPDATE OrderLines1
    SET price =
    (SELECT MAX(price)
    FROM OrderLines2
    WHERE LEFT(order_number,10)
    = LEFT(OrderLines1.order_number,10)
    AND line_number = OrderLines1.line_number)

    INSERT INTO OrderLines1 (order_number, line_number, price)
    SELECT O2.order_number, O2.line_number, O2.price
    FROM OrderLines2 AS O2
    LEFT JOIN OrderLines1 AS O1
    ON LEFT(O1.order_number,10) = LEFT(O2.order_number,10)
    AND O1.line_number = O2.line_number
    WHERE O1.order_number IS NULL

    END TRAN

    See BEGIN TRAN and SET TRANSACTION ISOLATION LEVEL in Books Online for
    details.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  5. #5

    Default Re: Tough SQL!

    declare order_counter int
    select order_counter = 0
    While order_counter < 5

    BEGIN
    UPDATE order_detail_org
    SET order_detail_org.order_unit_price =
    order_detail_chg.order_unit_price,
    order_detail_org.order_line_cd = order_detail_chg.order_line_cd,
    order_detail_org.order_line_qty = order_detail_chg.order_line_qty,
    order_detail_org.order_item_desc = order_detail_chg.order_item_desc,
    order_detail_org.vendor_part_num = order_detail_chg.vendor_part_num,
    order_detail_org.order_line_amt = order_detail_chg.order_line_amt,
    order_detail_org.order_prep_logon =
    order_detail_chg.order_prep_logon,
    order_detail_org.order_uom_cd = order_detail_chg.order_uom_cd,
    order_detail_org.vendor_name = order_detail_chg.vendor_name,
    order_detail_org.user_name = order_detail_chg.user_name

    from order_detail_chg

    inner join order_detail_org

    on left(order_detail_org.order_num, 10) =
    left(order_detail_chg.order_num, 10)

    and order_detail_org.order_line_num =
    order_detail_chg.order_line_num
    and Right(order_detail_chg.order_num,2) = order_counter

    insert into
    order_detail_org
    (order_num, order_dept_cd, order_line_cd, order_line_num,
    order_line_qty, order_item_desc,
    order_unit_price, vendor_part_num, order_line_amt, order_prep_logon,
    order_uom_cd, vendor_name, user_name, vendor_id, order_auth_dt,
    doc_subject)
    SELECT
    chg.order_num,
    chg.order_dept_cd,
    chg.order_line_cd,
    chg.order_line_num,
    chg.order_line_qty,
    chg.order_item_desc,
    chg.order_unit_price,
    chg.vendor_part_num,
    chg.order_line_amt,
    chg.order_prep_logon,
    chg.order_uom_cd,
    chg.vendor_name,
    chg.user_name,
    chg.vendor_id,
    chg.order_auth_dt,
    chg.doc_subject

    FROM
    order_detail_chg chg
    LEFT OUTER JOIN
    order_detail_org org
    ON
    LEFT(org.order_num, 11) = LEFT(chg.order_num, 11) AND
    org.order_line_num = chg.order_line_num
    WHERE
    org.order_line_num IS NULL

    and (right(chg.order_num,2)) = order_counter

    Select order_counter = order_counter + 1
    End
    GO


    University of California at Merced


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Yaheya Guest

  6. #6

    Default Re: Tough SQL!

    Was that a question?

    I don't understand why you want to execute this code 5 times. The INSERT and
    UPDATE statements are set-based operations that apply to all rows. There's
    no point in putting the INSERT and UPDATE in a loop. Did the code I posted
    not give the result you wanted? If not, can you post some sample data as
    INSERT statements (like I did) and give an example of your expected output.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Yaheya Quazi" <edu> wrote in message
    news:#phx.gbl... 


    David Guest

  7. #7

    Default Re: Tough SQL!

    David:

    I am sorry but not that was not a question, the code works just fine. I
    have run the code 5 times because review this sample data

    8908ABB23200 1
    8908ABB23201 1
    8908ABB23201 2
    8908ABB23202 1
    8908ABB23202 2

    You see I have a separate table that has all the ordernum ending with
    00.

    on first update where Right(order_num,2) = 01 the process will update 00
    line 1 with 01 line 1.

    I run an insert for where now I will have 01 line 2 in my table where
    only 00 records exists.

    then I go on to my second pass where I check for 02 and do update on my
    00 table with the above example I update 00 line 1 with 02 line 1

    do an insert but nothing is needed here because I have all the lines!

    I know my requirements sound crazy, but English is not my first language
    and I can't explain well. After all the above operation is done I get
    the following data in my table

    8908ABB23200 1
    8908ABB23201 2



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Yaheya Guest

Similar Threads

  1. Hm this is gonna be a tough one..
    By Pelleyoo in forum Macromedia Director 3D
    Replies: 8
    Last Post: August 25th, 05:54 PM
  2. tough one IXmlSerializable
    By Random in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 6th, 07:49 PM
  3. Reading Cookies (Tough one!)
    By Brandon in forum ASP Components
    Replies: 0
    Last Post: November 21st, 06:19 PM
  4. Old Toad Quiz: tough
    By mick white in forum Macromedia Dreamweaver
    Replies: 37
    Last Post: July 24th, 03:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139