SQL question (Autoincrementing value.)

From: Ron Stow <ronstow_at_btinternet.com>
Date: 2000/01/29
Message-ID: <86vd6a$je$1_at_neptunium.btinternet.com>#1/1

I am access a database for a Workflow system that records a historic list of events on 'Work Ojcects' in a History table, the problem is the system only stores the 'After Image' and a lot of the results we whant require specifying criteria if the before and after the event has taken place. To match records in SQL it would be easier if they where incrementing field value that I could join using (b.ID = a.ID+1)

***How do I insert an incrementing field value in an new table?***

I have include some simple SQL that would produce the results I don't know if it would work without knowing how to include an incrementing field.

Notes: the Index used to interrogate w20 table is crdattim, recordcd, crnode, enddattim. The first three are the unique key for workobjects within the workflow system and the last is the timestamp given after each transaction is complete, giving a chronological order to the events. (What I basically what to do is replace this time ordering value wiht a integer value to facilitate the joining mentioned above)

Hope I have made myself clear :¬)

Thanks in advance

Graeme.

Sql:

CREATE TABLE STOWG/W20EXTR

    (   ID          <incrementing fields>     int
        ,CRDATIM       CHAR( 26   )


       ,RECORDCD   CHAR( 1)

       ,CRNODE        CHAR(2)


       ,ENDDATTIM     CHAR(     26 )


        ,ENDWRKTYPE    CHAR(10)


       ,ENDQUEUECD   CHAR(8)


       ,ENDSTATCD       CHAR(10)

 )

INSERT INTO STOWG/W20EXTR
 (CRDATTIM,RECORDCD,CRNODE,ENDDATTIM,ENDWRKTYPE,ENDQUEUECD,ENDSTATCD) SELECT CRDATTIM, RECORCD, .....as above
FROM A21BSPDB/W20U999S WHERE

ENDDATTIM > '2000-01-21-00.00.00.000000'   AND
ENDDATTIM < '2000-01-22-00.00.00.000000'   AND
ENDWRKTYPE IN('.............. worktype list  ..);

ORDER BY CRDATTIM, RECORDCD, CRNODE, ENDDATTIM final select would do the matching with the previpous record in the list..

SELECT A.ENDWRKTYPE AS BEGWRKTYPE

                ,A.ENDQUEUECD AS BEGQUEUECD
               ,A.ENDSTATCD AS BEGSTATCD
                ,B.*
FROM
        STOWG/W20EXTR   A        INNER JOIN
        STOWG/W20EXTR   B        ON
A.CRDATTIM = B.CRDATTIM   AND
A.RECORDCD = B.RECORDCD  AND

A.CRNODE = B.CRNODE AND
B.ID = A.ID + 1
WHERE BEGQUEUECD = '.....begining queues....' AND

                ENDQUEUECD = ' .....after quuees.....' ;

Received on Sat Jan 29 2000 - 00:00:00 CET