Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    10

    Unanswered: inserting\update line numbering

    I have a table which contains order information, which I would like to have line number associated with them

    what SQL statement do I use in order to add the line numbering for each line, and have it dependent on reseting on the sales order number?

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    If this is a one-time operation, usually a temp table is created with an identity. In other cases you may need to add an identity column to the table.

  3. #3
    Join Date
    Jul 2004
    Posts
    10
    Quote Originally Posted by Kaiowas
    If this is a one-time operation, usually a temp table is created with an identity. In other cases you may need to add an identity column to the table.
    This is a daily build operation

    Using Identity though creates the numbering for all records in the table (sees it as one order (500 records, records numbered from 1 to 500?)

    I am looking at the line numbering to reset back to 1 everytime there is a change in the order number field (inv_ref field)

    this can't be done through identity?
    Last edited by clipper; 10-12-04 at 22:11.

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    My guess is, that it would be possible using an identity column, but I wouldn't go for that if it needs to be reset on a daily basis.

    It's probably me, but I'm still not quite clear on what you want, on the other hand, maybe I do but miss the point as to why you need a linenumber associated with the table contents.

    One of these might work for you though:
    - create a view that has a computed column (if the linenumber can be determined on other information from the table);
    - create a trigger that does an update (guess this can be quite a burden);
    - create an sp; do an update based on identity from a temp-table.

  5. #5
    Join Date
    Jul 2004
    Posts
    10
    line number id forms part of the primary key make up.

    I have Invoice number, sales order number, and line id

    I can't include product id instead of line id as in an invoice there may be a reference to the same product id i.e. at line 1 and 10.

    I guess a messy way of going about it is to just use identity and leave the count go on the entire table just to satisfy the primary key requirements.

Posting Permissions

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