SQL Determine next line number - IBM AS400

This is a discussion on SQL Determine next line number - IBM AS400 ; I have a table similar to this: CREATE TABLE TESTLIB.TESTTABL ( ITEM CHAR(15) NOT NULL, LINE NUMERIC(3,0) NOT NULL, CODE CHAR(3) NOT NULL, DESC CHAR(50) NOT NULL ) ; If I have the following records in the tabel: INSERT INTO ...

+ Reply to Thread
Results 1 to 6 of 6

Thread: SQL Determine next line number

  1. SQL Determine next line number

    I have a table similar to this:
    CREATE TABLE TESTLIB.TESTTABL
    ( ITEM CHAR(15) NOT NULL,
    LINE NUMERIC(3,0) NOT NULL,
    CODE CHAR(3) NOT NULL,
    DESC CHAR(50) NOT NULL ) ;

    If I have the following records in the tabel:
    INSERT INTO TESTLIB.TESTTABL
    (ITEM, LINE, CODE, DESC)
    VALUES ('ABC', 1, 'STU', 'APPLE RECORDS') ,
    ('ABC', 2, 'BTI', 'ARI') ,
    ('ABC', 3, 'MSR', 'STUNM') ,
    ('ADE', 1, 'BTI', 'WBS') ,
    ('ADE', 2, 'STU', 'WARRNER BROTHERS') ,
    ('ADE', 4, 'TRK', 'POOR BOY') ,
    ('ADE', 5, 'TRK', '1BURBON, 1 SCOTCH & 1 BEER') ,
    ('AEF', 1, 'AEC', 'STUNM') ,
    ('AGH', 1, 'AEC', 'STUNM') ;

    And I need to add the following records:
    ITEM LINE CODE DESC
    ADE ? AEC MSI
    AFG ? AEC STUNM
    AGH ? AEC STUNM

    Does someone know an easy way to determine the
    next line number to use in an insert statement?
    The line number always needs to be 1 or greater
    and cannot already exist in the file. So based
    on the data above, the line numbers I need to
    add are:
    ITEM LINE CODE DESC
    ADE 3 or 7 AEC MSI
    AFG 1 AEC STUNM
    AGH 2 AEC STUNM


    -Mike LaFountain
    mlafount@gmail.com


  2. Re: SQL Determine next line number

    Mike LaFountain writes:

    > I have a table similar to this:
    > CREATE TABLE TESTLIB.TESTTABL
    > ( ITEM CHAR(15) NOT NULL,
    > LINE NUMERIC(3,0) NOT NULL,
    > CODE CHAR(3) NOT NULL,
    > DESC CHAR(50) NOT NULL ) ;
    >
    > If I have the following records in the tabel:
    > INSERT INTO TESTLIB.TESTTABL
    > (ITEM, LINE, CODE, DESC)
    > VALUES ('ABC', 1, 'STU', 'APPLE RECORDS') ,
    > ('ABC', 2, 'BTI', 'ARI') ,
    > ('ABC', 3, 'MSR', 'STUNM') ,
    > ('ADE', 1, 'BTI', 'WBS') ,
    > ('ADE', 2, 'STU', 'WARRNER BROTHERS') ,
    > ('ADE', 4, 'TRK', 'POOR BOY') ,
    > ('ADE', 5, 'TRK', '1BURBON, 1 SCOTCH & 1 BEER') ,
    > ('AEF', 1, 'AEC', 'STUNM') ,
    > ('AGH', 1, 'AEC', 'STUNM') ;
    >
    > And I need to add the following records:
    > ITEM LINE CODE DESC
    > ADE ? AEC MSI
    > AFG ? AEC STUNM
    > AGH ? AEC STUNM
    >
    > Does someone know an easy way to determine the
    > next line number to use in an insert statement?
    > The line number always needs to be 1 or greater
    > and cannot already exist in the file. So based
    > on the data above, the line numbers I need to
    > add are:
    > ITEM LINE CODE DESC
    > ADE 3 or 7 AEC MSI
    > AFG 1 AEC STUNM
    > AGH 2 AEC STUNM
    >
    >
    > -Mike LaFountain
    > mlafount@gmail.com


    Hello Mike,

    You should be able to simply use the "INSERT using SELECT"
    form of the INSERT statement, and a max() to calculate
    the high line number.

    I don't have a DB2 db to test this on, but the syntax
    would be something like...

    * untested sql follows *

    INSERT INTO TESTTABL
    (ITEM, LINE, CODE, DESC)
    SELECT
    'ADE', (MAX(LINE) +1), 'AEC', 'MSI'
    FROM TESTTABL
    WHERE ITEM = 'ADE'


    You may also want to reconsider having a column named 'DESC'
    as that is often used as a SQL keyword and may cause confusing
    errors/problems.

    I hope that helps,
    Carl.

  3. Re: SQL Determine next line number

    Carl writes:

    > Mike LaFountain writes:
    >
    >> I have a table similar to this:
    >> CREATE TABLE TESTLIB.TESTTABL
    >> ( ITEM CHAR(15) NOT NULL,
    >> LINE NUMERIC(3,0) NOT NULL,
    >> CODE CHAR(3) NOT NULL,
    >> DESC CHAR(50) NOT NULL ) ;
    >>
    >> If I have the following records in the tabel:
    >> INSERT INTO TESTLIB.TESTTABL
    >> (ITEM, LINE, CODE, DESC)
    >> VALUES ('ABC', 1, 'STU', 'APPLE RECORDS') ,
    >> ('ABC', 2, 'BTI', 'ARI') ,
    >> ('ABC', 3, 'MSR', 'STUNM') ,
    >> ('ADE', 1, 'BTI', 'WBS') ,
    >> ('ADE', 2, 'STU', 'WARRNER BROTHERS') ,
    >> ('ADE', 4, 'TRK', 'POOR BOY') ,
    >> ('ADE', 5, 'TRK', '1BURBON, 1 SCOTCH & 1 BEER') ,
    >> ('AEF', 1, 'AEC', 'STUNM') ,
    >> ('AGH', 1, 'AEC', 'STUNM') ;
    >>
    >> And I need to add the following records:
    >> ITEM LINE CODE DESC
    >> ADE ? AEC MSI
    >> AFG ? AEC STUNM
    >> AGH ? AEC STUNM
    >>
    >> Does someone know an easy way to determine the
    >> next line number to use in an insert statement?
    >> The line number always needs to be 1 or greater
    >> and cannot already exist in the file. So based
    >> on the data above, the line numbers I need to
    >> add are:
    >> ITEM LINE CODE DESC
    >> ADE 3 or 7 AEC MSI
    >> AFG 1 AEC STUNM
    >> AGH 2 AEC STUNM
    >>
    >>
    >> -Mike LaFountain
    >> mlafount@gmail.com

    >
    > Hello Mike,
    >
    > You should be able to simply use the "INSERT using SELECT"
    > form of the INSERT statement, and a max() to calculate
    > the high line number.
    >
    > I don't have a DB2 db to test this on, but the syntax
    > would be something like...
    >
    > * untested sql follows *
    >
    > INSERT INTO TESTTABL
    > (ITEM, LINE, CODE, DESC)
    > SELECT
    > 'ADE', (MAX(LINE) +1), 'AEC', 'MSI'
    > FROM TESTTABL
    > WHERE ITEM = 'ADE'
    >
    >
    > You may also want to reconsider having a column named 'DESC'
    > as that is often used as a SQL keyword and may cause confusing
    > errors/problems.
    >
    > I hope that helps,
    > Carl.


    Mike,

    I just wanted to add that you will need to handle the instances
    where no records exits for the given key (ITEM) and the
    max() function return null, where I believe the value
    assigned to LINE will be the default (0 in your case) where
    you really want a 1. Setting the default value for the
    LINE field to 1 should do the trick.

    There are, of course, many other ways to handle this.

    Carl.

  4. Re: SQL Determine next line number

    Thanks Carl,

    I didn't think I could get the MAX() bif to work because
    of the GROUP BY clause requirement, but I did. I created a
    second insert statement to handle the "Null" records. Below
    is a little more code to demonstrate what I came up with:

    --Create the records to be added --
    CREATE TABLE TESTLIB.NEWDATA
    ( NEWITEM CHAR(15) NOT NULL,
    NEWDESC CHAR(50) NOT NULL ) ;

    INSERT INTO TESTLIB.NEWDATA
    (NEWITEM, NEWDESC)
    VALUES ('ADE', 'APPLE RECORDS') ,
    ('AFG', 'ARI') ,
    ('AGH', 'STUNM') ;

    -- Insert records where item is in target file --
    INSERT INTO TESTLIB.TESTTABL
    (ITEM, LINE, CODE, DESC)
    SELECT NEWITEM,
    (Max(a.LINE)+1),
    'STU',
    NEWDESC
    FROM TESTLIB.NEWDATA, TESTLIB.TESTTABL a
    WHERE NEWITEM = a.ITEM
    AND a.ITEM IN
    (SELECT b.ITEM FROM TESTLIB.TESTTABL b)
    GROUP BY NEWITEM, NEWDESC ;


    -- Insert records where item is not in target file --
    INSERT INTO TESTLIB.TESTTABL
    (ITEM, LINE, CODE, DESC)
    SELECT NEWITEM,
    1,
    'STU',
    NEWDESC
    FROM TESTLIB.NEWDATA
    WHERE NEWITEM NOT IN
    (SELECT ITEM FROM TESTLIB.TESTTABL) ;


  5. Re: SQL Determine next line number

    Rather than messing with defaults [¿which will not directly resolve
    the NULL situation anyway?], just choose/set the value of LINE=1 to
    replace the NULL value using COALESCE() in the INSERT statement:

    INSERT INTO TESTTABL
    (ITEM, LINE, CODE, DESC)
    SELECT 'NEW' ,coalesce(MAX(LINE) +1 , 1),'XXX' ,'YYY'
    FROM TESTTABL
    WHERE ITEM = 'NEW'

    INSERT using VALUES instead of SELECT can be done instead, moving the
    SELECT as Nested Table Expression for the column representing LINE:

    INSERT INTO TESTTABL
    (ITEM, LINE, CODE, DESC)
    VALUES (
    'DOH', (select coalesce(MAX(LINE) +1, 1) as LINE
    from TESTTABL
    where ITEM='DOH')
    ,'AAA', 'BBB' )

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    Carl wrote:
    >>
    >> You should be able to simply use the "INSERT using SELECT"
    >> form of the INSERT statement, and a max() to calculate
    >> the high line number.
    >>
    >> I don't have a DB2 db to test this on, but the syntax
    >> would be something like...
    >>
    >> * untested sql follows *
    >>
    >> INSERT INTO TESTTABL
    >> (ITEM, LINE, CODE, DESC)
    >> SELECT
    >> 'ADE', (MAX(LINE) +1), 'AEC', 'MSI'
    >> FROM TESTTABL
    >> WHERE ITEM = 'ADE'
    >>
    >>
    >> You may also want to reconsider having a column named 'DESC'
    >> as that is often used as a SQL keyword and may cause confusing
    >> errors/problems.
    >>
    >> I hope that helps,
    >> Carl.

    >
    > Mike,
    >
    > I just wanted to add that you will need to handle the instances
    > where no records exits for the given key (ITEM) and the
    > max() function return null, where I believe the value
    > assigned to LINE will be the default (0 in your case) where
    > you really want a 1. Setting the default value for the
    > LINE field to 1 should do the trick.
    >
    > There are, of course, many other ways to handle this.
    >
    > Carl.


  6. Re: SQL Determine next line number

    On Jul 31, 11:04 am, CRPence wrote:
    > Rather than messing with defaults [¿which will not directly resolve
    > the NULL situation anyway?], just choose/set the value of LINE=1 to
    > replace the NULL value using COALESCE() in the INSERT statement:
    >
    > INSERT INTO TESTTABL
    > (ITEM, LINE, CODE, DESC)
    > SELECT 'NEW' ,coalesce(MAX(LINE) +1 , 1),'XXX' ,'YYY'
    > FROM TESTTABL
    > WHERE ITEM = 'NEW'
    >
    > INSERT using VALUES instead of SELECT can be done instead, moving the
    > SELECT as Nested Table Expression for the column representing LINE:
    >
    > INSERT INTO TESTTABL
    > (ITEM, LINE, CODE, DESC)
    > VALUES (
    > 'DOH', (select coalesce(MAX(LINE) +1, 1) as LINE
    > from TESTTABL
    > where ITEM='DOH')
    > ,'AAA', 'BBB' )
    >
    > Regards, Chuck
    > --



    Thanks Chuck! The coalesce knocks it down to just one insert
    statement:

    INSERT INTO TESTLIB.TESTTABL
    (ITEM, LINE, CODE, DESC)
    SELECT NEWITEM,
    COALESCE((Max(LINE)+1),1),
    'STU',
    NEWDESC
    FROM TESTLIB.NEWDATA
    LEFT JOIN TESTLIB.TESTTABL
    ON NEWITEM = ITEM
    GROUP BY NEWITEM, NEWDESC ;

    --Mike LaFountain


+ Reply to Thread