Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    11

    Unanswered: how to update a table in oracle using value from next record

    i have the following table in Oracle 8i..
    time_card (data got from sql ldr from text file)
    emp_no, work_date, time1, time2, time_type
    11 20/06/2011 07:00 null 1
    11 20/06/2011 16:00 null 3
    11 21/06/2011 06:55 null 1
    11 21/06/2011 16:05 null 3
    12 20/06/2011 06:55 null 1
    12 20/06/2011 16:55 null 3

    I want to update the table in a way it has the following records and delete unwanted records.
    emp_no, work_date, time1, time2, time_type
    11 20/06/2011 07:00 16:00 1
    11 21/06/2011 06:55 16:05 1
    12 20/06/2011 06:55 16:55 1

    Please advise how to do it with the proper code.
    Also advise how I can do it using staging table using sqlldr.

    Please it is very urgent.
    Thank you in advance.

    MACK

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >i have the following table in Oracle 8i..
    BTW - 8i was obsoleted & unsupported last Century.

    >Please advise how to do it with the proper code.
    >Also advise how I can do it using staging table using sqlldr.
    I am confused. sqlldr is never used to manipulate data within existing table.
    No code is involved with invoking sqlldr.

    >Please it is very urgent.
    Why is it urgent for me to provide solution to for this
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It would have helped if you provided some more info (such as column's data types), but OK - here it is. The table looks like this:
    Code:
    SQL> desc time_card;
     Name                          Null?    Type
     ----------------------------- -------- --------------------
     EMP_NO                                 NUMBER
     WORK_DATE                              DATE
     TIME1                                  DATE
     TIME2                                  DATE
     TIME_TYPE                              NUMBER
    SQL*Loader's control file:
    Code:
    load data
    infile *
    into table time_card
    replace
    fields terminated by whitespace
    (emp_no,
     work_date "to_date(:work_date, 'dd/mm/yyyy')",
     time1     "to_date(:time1, 'hh24:mi')",
     time2     "decode(:time2, 'null', null)",
     time_type
    )
    
    begindata
    11 20/06/2011 07:00 null 1
    11 20/06/2011 16:00 null 3
    11 21/06/2011 06:55 null 1
    11 21/06/2011 16:05 null 3
    12 20/06/2011 06:55 null 1
    12 20/06/2011 16:55 null 3
    Loading session:
    Code:
    C:\temp>sqlldr scott/tiger control=test.ctl log=test.log
    
    SQL*Loader: Release 10.2.0.1.0 - Production on Cet Lip 23 18:08:48
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Commit point reached - logical record count 5
    Commit point reached - logical record count 6
    
    C:\temp>
    What have we loaded?
    Code:
    SQL> select emp_no,
      2    work_date,
      3    to_char(time1, 'hh24:mi') time1,
      4    to_char(time2, 'hh24:mi') time2,
      5    time_type
      6  from time_card
      7  order by emp_no, work_date, time1;
    
        EMP_NO WORK_DATE  TIME1 TIME2  TIME_TYPE
    ---------- ---------- ----- ----- ----------
            11 20.06.2011 07:00                1
            11 20.06.2011 16:00                3
            11 21.06.2011 06:55                1
            11 21.06.2011 16:05                3
            12 20.06.2011 06:55                1
            12 20.06.2011 16:55                3
    
    6 rows selected.
    
    SQL>
    Let's update it:
    Code:
    SQL> update time_card t set
      2    t.time2 = (select t2.time1
      3               from time_card t2
      4               where t2.emp_no = t.emp_no
      5                 and t2.work_date = t.work_date
      6                 and t2.time1 > t.time1
      7               );
    
    6 rows updated.
    
    SQL> select emp_no,
      2    work_date,
      3    to_char(time1, 'hh24:mi') time1,
      4    to_char(time2, 'hh24:mi') time2,
      5    time_type
      6  from time_card
      7  order by emp_no, work_date, time1;
    
        EMP_NO WORK_DATE  TIME1 TIME2  TIME_TYPE
    ---------- ---------- ----- ----- ----------
            11 20.06.2011 07:00 16:00          1
            11 20.06.2011 16:00                3
            11 21.06.2011 06:55 16:05          1
            11 21.06.2011 16:05                3
            12 20.06.2011 06:55 16:55          1
            12 20.06.2011 16:55                3
    
    6 rows selected.
    
    SQL>
    Delete records we don't need any more:
    Code:
    SQL> delete from time_card
      2  where time2 is null;
    
    3 rows deleted.
    Finally, the result:
    Code:
    SQL> select emp_no,
      2    work_date,
      3    to_char(time1, 'hh24:mi') time1,
      4    to_char(time2, 'hh24:mi') time2,
      5    time_type
      6  from time_card
      7  order by emp_no, work_date, time1;
    
        EMP_NO WORK_DATE  TIME1 TIME2  TIME_TYPE
    ---------- ---------- ----- ----- ----------
            11 20.06.2011 07:00 16:00          1
            11 21.06.2011 06:55 16:05          1
            12 20.06.2011 06:55 16:55          1
    
    SQL>

  4. #4
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53
    check out lag/lead analytical functions
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

  5. #5
    Join Date
    Jun 2011
    Posts
    11

    Loading data using sqlldr

    Hi. thanks for the reply...Appreciate it..
    But the update command does not work for a scenario where there is only one 1 entry without exit or 1 exit without a corresponding entry.

    This will work only for 1 entry having a corresponding exit.

    Please let me know how i can overcome this..

    Also appreciate If u can tell me how i can also include a multiple exit/entry for a same day based on a time_type of 2/4 for exit/entry. And have data for more than 1 date.

    A sample data as follows:
    001,0000000071,01,2011/09/20,15:02,
    001,0000000501,01,2011/09/20,15:25,
    001,0000000502,01,2011/09/20,15:25,
    001,0000000015,01,2011/09/20,15:26,
    001,0000000019,01,2011/09/20,15:33,
    001,0000000060,03,2011/09/20,15:33,
    001,0000000008,03,2011/09/20,15:35,
    001,0000000009,01,2011/09/20,15:53,
    001,0000000060,03,2011/09/20,15:33,
    001,0000000008,03,2011/09/21,15:35,
    001,0000000009,01,2011/09/21,15:53,


    Quote Originally Posted by Littlefoot View Post
    It would have helped if you provided some more info (such as column's data types), but OK - here it is. The table looks like this:
    Code:
    SQL> desc time_card;
     Name                          Null?    Type
     ----------------------------- -------- --------------------
     EMP_NO                                 NUMBER
     WORK_DATE                              DATE
     TIME1                                  DATE
     TIME2                                  DATE
     TIME_TYPE                              NUMBER
    SQL*Loader's control file:
    Code:
    load data
    infile *
    into table time_card
    replace
    fields terminated by whitespace
    (emp_no,
     work_date "to_date(:work_date, 'dd/mm/yyyy')",
     time1     "to_date(:time1, 'hh24:mi')",
     time2     "decode(:time2, 'null', null)",
     time_type
    )
    
    begindata
    11 20/06/2011 07:00 null 1
    11 20/06/2011 16:00 null 3
    11 21/06/2011 06:55 null 1
    11 21/06/2011 16:05 null 3
    12 20/06/2011 06:55 null 1
    12 20/06/2011 16:55 null 3
    Loading session:
    Code:
    C:\temp>sqlldr scott/tiger control=test.ctl log=test.log
    
    SQL*Loader: Release 10.2.0.1.0 - Production on Cet Lip 23 18:08:48
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Commit point reached - logical record count 5
    Commit point reached - logical record count 6
    
    C:\temp>
    What have we loaded?
    Code:
    SQL> select emp_no,
      2    work_date,
      3    to_char(time1, 'hh24:mi') time1,
      4    to_char(time2, 'hh24:mi') time2,
      5    time_type
      6  from time_card
      7  order by emp_no, work_date, time1;
    
        EMP_NO WORK_DATE  TIME1 TIME2  TIME_TYPE
    ---------- ---------- ----- ----- ----------
            11 20.06.2011 07:00                1
            11 20.06.2011 16:00                3
            11 21.06.2011 06:55                1
            11 21.06.2011 16:05                3
            12 20.06.2011 06:55                1
            12 20.06.2011 16:55                3
    
    6 rows selected.
    
    SQL>
    Let's update it:
    Code:
    SQL> update time_card t set
      2    t.time2 = (select t2.time1
      3               from time_card t2
      4               where t2.emp_no = t.emp_no
      5                 and t2.work_date = t.work_date
      6                 and t2.time1 > t.time1
      7               );
    
    6 rows updated.
    
    SQL> select emp_no,
      2    work_date,
      3    to_char(time1, 'hh24:mi') time1,
      4    to_char(time2, 'hh24:mi') time2,
      5    time_type
      6  from time_card
      7  order by emp_no, work_date, time1;
    
        EMP_NO WORK_DATE  TIME1 TIME2  TIME_TYPE
    ---------- ---------- ----- ----- ----------
            11 20.06.2011 07:00 16:00          1
            11 20.06.2011 16:00                3
            11 21.06.2011 06:55 16:05          1
            11 21.06.2011 16:05                3
            12 20.06.2011 06:55 16:55          1
            12 20.06.2011 16:55                3
    
    6 rows selected.
    
    SQL>
    Delete records we don't need any more:
    Code:
    SQL> delete from time_card
      2  where time2 is null;
    
    3 rows deleted.
    Finally, the result:
    Code:
    SQL> select emp_no,
      2    work_date,
      3    to_char(time1, 'hh24:mi') time1,
      4    to_char(time2, 'hh24:mi') time2,
      5    time_type
      6  from time_card
      7  order by emp_no, work_date, time1;
    
        EMP_NO WORK_DATE  TIME1 TIME2  TIME_TYPE
    ---------- ---------- ----- ----- ----------
            11 20.06.2011 07:00 16:00          1
            11 21.06.2011 06:55 16:05          1
            12 20.06.2011 06:55 16:55          1
    
    SQL>

Posting Permissions

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