Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    6

    Unanswered: Insert row with newest date

    Hi

    i have a table with many numbers.

    i need to insert

    always the newest row into another table.

    Code:
    INSERT INTO Table_Temp1(uebnahmdatum,erfnr,schltermart,termdatum,
    abtlg,evaschl,shart,ereigdat,ereiguhr,abfpapart,abfpapnr,ausstell)
    SELECT FIRST 1
    FROM Table_Temp2
    ORDER BY erfnr,ereigdat,ereiguhr
    and yes i know that i cant order in a insert statement

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi Taccu

    what is your problem and what is the question ?

    Just extrapolating: I am not sure that "select first" is understood by 7.31.

    According to your scenario, if the newest row has been updated in the same process,
    I think you should use the sqlca.sqlerrd structure to find out the rowid of the last inserted row.

    Your SELECT statement has no columns selected, except the value "1", which is probably not what you want.

    I would proceed like this:
    Code:
    SELECT ( FIRST if aplicabale in your version) erfnr,ereigdat,ereiguhr,rowid
    INTO variableslist
    FROM table_temp2
    ORDER BY erfnr,ereigdat,ereiguhr ( DESC ? )
    
    then
    
    INSERT INTO Table_Temp1(uebnahmdatum,erfnr,schltermart,termdatum,
    abtlg,evaschl,shart,ereigdat,ereiguhr,abfpapart,abfpapnr,ausstell)
    SELECT RelevantColumnsList
    FROM table_temp2
    WHERE rowid = RowidVariable
    A bit heavy, but it should work. Check that the order columns are indexed.

    But definately apply the first solution if the "newest row" has been inserted in the same program.

    cheers
    Eric

  3. #3
    Join Date
    Feb 2012
    Posts
    6
    Hi,

    here is the solution


    Code:
    ResultSet rs = zweiterbefehl.executeQuery(query1);
            while(rs.next()){
            b = rs.getString("erfnr");
            System.out.println(b);
            }
            ResultSet rst = zweiterbefehl.executeQuery("SELECT FIRST 1 ereigdat\n"
                   + "FROM Table_temp2\n"
                   + "WHERE erfnr=" + "'" + b + "'" +"\n"
                   + "ORDER BY erfnr,ereigdat,ereiguhr;");
            while(rst.next()){
            c = rst.getString("ereigdat");
            System.out.println(c);
            }
            ResultSet rsa = zweiterbefehl.executeQuery("SELECT FIRST 1 ereiguhr\n"
                   + "FROM Table_temp2\n"
                   + "WHERE erfnr=" + "'" + b +"'" +"\n"
                   + "ORDER BY erfnr,ereigdat,ereiguhr;");
            while(rsa.next()){
            d = rsa.getString("ereiguhr");
            System.out.println(d);}
        zweiterbefehl.executeUpdate("INSERT INTO Table_Temp1(uebnahmdatum,erfnr,schltermart,"
    + "termdatum,"
    + "abtlg,evaschl,shart,ereigdat,ereiguhr,abfpapart,"
    + "abfpapnr,ausstell,lfdabfnr,lfdspaufnr)\n"
    + "SELECT uebnahmdatum,erfnr,schltermart,"
    + "termdatum,abtlg,evaschl,shart\n"
    + ",ereigdat,ereiguhr,abfpapart,"
    + "abfpapnr,ausstell,lfdabfnr,lfdspaufnr\n"
    + "FROM Table_temp2\n"
    + "WHERE erfnr =" +"'" +b +"'" +"\n"
    + "and ereigdat = " + "'" +c +"'" +"\n"
    + "and ereiguhr = " +"'" +d +"';" +"\n" );

Posting Permissions

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