erik@abblund.se (Erik Sparre) (10/25/90)
I have a problem. The user entered 900 rows into table. Instead of entering the elapsed time in hours and minutes he entered the wall clock time of completion. (The system registers flying times for aeroplanes) I have the start wall clock time, so it seems possible to recalculate the erroneous field with a simple expression like: for each row do elapsed_time := elapsed_time - start_time example: Start time was 12:32 and landing time was 13:45. The elapsed time should be 1:13, but was entered as 13:45 I can see two possibilities. Either doing the change with an SQL-statement, or writing a program to do it. I haven't much experience with SQL, so maybe you could give me some hints on how to solve it in SQL or with a program using SQL facilities. Oh, BTW, I'm using ORACLE. This is a private posting and has nothing to do with ABB. Thanks in advance ! -- Erik Sparre | Email erik@abblund.se ABB Corporate Research | Phone +46 46 168527 Dept. KLL, Lab for Man-Machine Communication | Telex 33709 IDEON Research Park, S-223 70 LUND, SWEDEN | Fax +46 46 145620
drack@titan.tsd.arlut.utexas.edu (Dave Rackley) (10/26/90)
In article <1990Oct25.081639.14155@abblund.se> erik@abblund.se (Erik Sparre) writes: [stuff deleted] > for each row do > elapsed_time := elapsed_time - start_time > example: > Start time was 12:32 and landing time was 13:45. > The elapsed time should be 1:13, but was entered as 13:45 You're real close! Use the update command: update table_name set elapsed time = elapsed_time - start_time; You may have to use the ORACLE function to_date() if the fields in question are NOT already in ORACLE date format. Hope this helps. :-) -- DISCLAIMER? I don't know anything 'bout any ol' disclaimer! +=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=+ | David Rackley | | | Applied Research Laboratories | Remember, you can tuna fish, | | The University of Texas | But you can't tuna piano! | | Austin, TX. 78758 | | +=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=+
eric@wdl47.wdl.fac.com (Eric Kuhnen) (10/26/90)
erik@abblund.se (Erik Sparre) writes: >I have a problem. >The user entered 900 rows into table. Instead of entering the elapsed time >in hours and minutes he entered the wall clock time of completion. >(The system registers flying times for aeroplanes) >I have the start wall clock time, so it seems possible to recalculate the >erroneous field with a simple expression like: >for each row do > elapsed_time := elapsed_time - start_time >I can see two possibilities. Either doing the change with an SQL-statement, >or writing a program to do it. I just tested this with integers on my INGRES system, but the solution should hold. update <table-name> set elapsed_time = elapsed_time - start_time You can do this right from the SQL*PLUS command line. "Q"
itkin@mrspoc.Transact.COM (Steven M. List) (10/26/90)
erik@abblund.se (Erik Sparre) writes: >The user entered 900 rows into table. Instead of entering the elapsed time >in hours and minutes he entered the wall clock time of completion. >(The system registers flying times for aeroplanes) >I have the start wall clock time, so it seems possible to recalculate the >erroneous field with a simple expression like: >for each row do > elapsed_time := elapsed_time - start_time >example: > Start time was 12:32 and landing time was 13:45. > The elapsed time should be 1:13, but was entered as 13:45 Unless I'm missing something painfully obvious, shouldn't this just be update <table> set elapsed_time to elapsed_time - start_time; Hmm? -- +----------------------------------------------------------------------------+ : Steven List @ Transact Software, Inc. :^>~ : : Chairman, Unify User Group of Northern California : : {apple,coherent,limbo,mips,pyramid,ubvax}!itkin@guinan.Transact.COM :
ravi@pds3 (Gorur R. Ravi) (11/01/90)
In article <1990Oct25.081639.14155@abblund.se> erik@abblund.se (Erik Sparre) writes: >I have a problem. > >The user entered 900 rows into table. Instead of entering the elapsed time >in hours and minutes he entered the wall clock time of completion. >(The system registers flying times for aeroplanes) >I have the start wall clock time, so it seems possible to recalculate the >erroneous field with a simple expression like: > >for each row do > elapsed_time := elapsed_time - start_time > > >I can see two possibilities. Either doing the change with an SQL-statement, >or writing a program to do it. > >I haven't much experience with SQL, so maybe you could give me some hints >on how to solve it in SQL or with a program using SQL facilities. > >Oh, BTW, I'm using ORACLE. > I would write a small Pro*C program for problems like these. There might be an easier way to do this which I can't think of at this moment. You need to select ROWID in your SQL statement and get records using FETCH statement. Then update records in a while loop using ROWID in the WHERE clause. A partial code might look like this. EXEC SQL DECLARE C1 CURSOR FOR SELECT ROWID FROM <TAB>; EXEC SQL OPEN C1; EXEC SQL WHENEVER NOT FOUND GOTO FINISH; while(1) { EXEC SQL FETCH C1 INTO :rowid; rowid.arr[rowid.len] = '\0'; EXEC SQL UPDATE <TAB> SET ERR_FLD = <expr> WHERE ROWID = :rowid; } FINISH: EXEC SQL CLOSE C1; As I have mentioned in the beginning there might be an easier way to do the same in sqlplus. So, check other options before using this. Hope this might help. Good luck! -- Gorur Ravi (ravi) uunet!pds3!ravi Project Design Systems, Inc., 2231 Crystal Drive, Suite 1114, Arlington, VA 22202
eric@wdl47.wdl.fac.com (Eric Kuhnen) (11/06/90)
ravi@pds3 (Gorur R. Ravi) writes: > I would write a small Pro*C program for problems like these. There >might be an easier way to do this which I can't think of at this moment. >You need to select ROWID in your SQL statement and get >records using FETCH statement. Then update records in a while loop using >ROWID in the WHERE clause. A partial code might look like this. > EXEC SQL DECLARE C1 CURSOR FOR SELECT ROWID FROM <TAB>; > EXEC SQL OPEN C1; > EXEC SQL WHENEVER NOT FOUND GOTO FINISH; > while(1) > { > EXEC SQL FETCH C1 INTO :rowid; > rowid.arr[rowid.len] = '\0'; > > EXEC SQL UPDATE <TAB> SET ERR_FLD = <expr> WHERE > ROWID = :rowid; > } > FINISH: EXEC SQL CLOSE C1; I think you'll find that the SQL "update" command will do everything that you've outlined here, and with less confusion. The solution is: update <tablename> set elapsed_time = elapsed_time - starting_time; No cursors, rowids, fetch loops, muss, or fuss. One simple command does it all! Plus, if you act now, we'll throw in this wonderful . . . . Well, you get the idea. "Q"