Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2008
    Posts
    4

    Question Unanswered: How to maintain transaction order for perpetual inventory

    Hello

    I would appreciate If any one could help me in this matter.

    problem is : how to maintain perpetual inventory transaction table order in batch mode updation ?.

    I have designed a table to hold all inventory transactions. The table order is perfectly maintained in online system of updation. But if I go with batch updation then the order of the transaction is collapsing. For example consider the following table design. (note I used auto number to maintain the order).

    version used : SQL Server 2000 with service pack updates.


    LINEID TRANS_TYPE TRANS_ID TRAN_DATE STOCKNO QUANTITY RATE

    1 Receipts 2 01/01/2000 xxxxx1 2 2.0
    2. Issue 1 01/01/2000 yyyyy1 1
    3 Receipts 3 02/01/2000 xxxxx2 4 3.0
    4 Issue 2 02/01/2000 yyyyy2 1

    the above is well in online mode. But in batch mode if i update issue 2 before receipts 3 then i am getting order like below

    1 Receipts 2 01/01/2000 xxxxx1 2 2.0
    2. Issue 1 01/01/2000 yyyyy1 1
    3 Issue 2 02/01/2000 yyyyy2 1
    4 Receipts 3 02/01/2000 xxxxx2 4 3.0

    ofcourse if the order collapse means costing can not be accurate. so please any one could help me to solve this problem. because many software packages are not posting in sequence if we choose in batch mode.
    Last edited by abu_siraj@yahoo.c; 02-09-08 at 13:03.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    i have little idea what your problem is, but in relational theory, which relational database management systems are based on, the order of the data in a database has no meaning. there really is no order and if your program depends on the order of the data, than your design is fatally flawed.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sequence of rows can be achieved with timestamping

    what's the primary key of your table? how come you have two transactions with the same transaction id?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote: "I used auto number to maintain the order". I expect that's where you are going wrong. Execution order is not guaranteed. You won't necessarily see the IDENTITY values inserted in the order you expect unless you insert single rows at a time using only a single connection.

    Do not try to assign any significance at all to the values of an IDENTITY column. If you do, then sooner or later it will cause you grief.

  5. #5
    Join Date
    Feb 2008
    Posts
    4

    update

    Quote Originally Posted by abu_siraj@yahoo.c
    Hello

    I would appreciate If any one could help me in this matter.

    problem is : how to maintain perpetual inventory transaction table order in batch mode updation ?.

    I have designed a table to hold all inventory transactions. The table order is perfectly maintained in online system of updation. But if I go with batch updation then the order of the transaction is collapsing. For example consider the following table design. (note I used auto number to maintain the order).

    version used : SQL Server 2000 with service pack updates.


    LINEID TRANS_TYPE TRANS_ID TRAN_DATE STOCKNO QUANTITY RATE

    1 Receipts 2 01/01/2000 xxxxx1 2 2.0
    2. Issue 1 01/01/2000 yyyyy1 1
    3 Receipts 3 02/01/2000 xxxxx2 4 3.0
    4 Issue 2 02/01/2000 yyyyy2 1

    the above is well in online mode. But in batch mode if i update issue 2 before receipts 3 then i am getting order like below

    1 Receipts 2 01/01/2000 xxxxx1 2 2.0
    2. Issue 1 01/01/2000 yyyyy1 1
    3 Issue 2 02/01/2000 yyyyy2 1
    4 Receipts 3 02/01/2000 xxxxx2 4 3.0

    ofcourse if the order collapse means costing can not be accurate. so please any one could help me to solve this problem. because many software packages are not posting in sequence if we choose in batch mode.
    The lineid is used as primary key. if multiuse environment this choice is flawed as auto number generation is abrupt in all sql server databases. so I think using datetime is the perfect choise for making transaction sequence.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, but only if you allow for "ties" in terms of the DATETIME value (and assign some other column as the "tiebreaker"). If you insert multiple rows at once, they will all have the same DATETIME value, so you need to code defensively. I'd recommend using a second column to avoid this problem, possibly a GUID or an IDENTITY (sequence) to guarantee uniqueness.

    -PatP

Posting Permissions

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