Help needed!
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 33

Thread: Help needed!

  1. #1
    Join Date
    Jun 2000
    Posts
    315

    Help needed!

    I have two inline views with start_time and end_time in each of them. Now I need to get the difference: end_time - start_time when end_time > max(start_time). Max(start_time) means the start_time right before the end_time.

    Basically, what I need to do is:
    update table a set column b =
    (select case vu1.end_time > max(vu2.start_time)
    then vu1.end_time - max(vu2.start_time)
    else 0
    end
    from vu1, vu2)
    where b is null;

    The problem is this is not a single group function.

    Any other way I can accomplish this?

    Thanks!

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Can you give some example?

    Tamil

  3. #3
    Join Date
    Jun 2000
    Posts
    315
    Hi, Tamil:

    The history table has column call_start_time, key_status. When key_status is OFF, we call it call_end_time; when key_status = ON, we call it call_start_time. Duration is the difference between call_end_time and the most recent call_start_time, which is max(call_start_time). The following code cannot use max function. But how could I get difference between call_end_time and the most recent call_start_time? If this does not work, how about sql/plus?

    Thank you!

    Here is the code:

    Update history set duration =
    (select case when vu1.call_end_time > max(vu2.call_start_time)
    then vu1.call_end_time - max(vu2.call_start_time)
    elso 0
    end
    from
    (select (to_char(dt.full_date, 'yyyymmdd') call_end_time
    from history h1, date_dimension dt
    where h1.date_key = dt.date_key and h1.key_status = 'OFF')vu1,
    (select (to_char(dt.full_date, 'yyyymmdd') call_end_time
    from history h2, date_dimsion dt
    where h2.date_key = dt.date_key and h2.key_status = 'ON') vu2)

    where duration is null;

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If I understand right, then you want to update both end records and start records by finding the most recent start record (if this is an end record) or the next end record (if this is a start record) and using the difference between the start and end dates as the basis?

    I don't understand what you mean by "The history table has column call_start_time, key_status. When key_status is OFF, we call it call_end_time; when key_status = ON, we call it call_start_time". Can you just post a DESC of the tables?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    "to_char(dt.full_date, 'yyyymmdd')" should not be needed, btw.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Jun 2000
    Posts
    315
    Sorry! There is another column called duration, which is the column I need to update. Duration is the difference between end_time and max(start_time). When key_status is OFF, call_start_time is defined as end_time; when key_status is ON, cal_start_time is defined as start_time.

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by slimdave
    "to_char(dt.full_date, 'yyyymmdd')" should not be needed, btw.
    LMAOPMP!!!!
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    but is there an actual column called end_time in the table, or is that just what the column "start_time" is to be interpreted as based on the value of key_status?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Jun 2000
    Posts
    315
    Yes, SlimDave!

    I used to_char for the full_date is because I need to caculate the duration using the time difference. Actually I need also hour, minutes, and seconds. I ignored those.

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by lgaorcl
    Yes, SlimDave!

    I used to_char for the full_date is because I need to caculate the duration using the time difference. Actually I need also hour, minutes, and seconds. I ignored those.
    Um ... you calculate the difference between two dates by subtracting one date from another, which gives you the day difference. If you subtract a char from a date then ...

    Code:
    SQL> select sysdate-to_char(sysdate-1) from dual;
    select sysdate-to_char(sysdate-1) from dual
                   *
    ERROR at line 1:
    ORA-01722: invalid number
    Is this a troll or something?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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



Click Here to Expand Forum to Full Width