Help needed!

[lgaorcl]

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!

[tamilselvan]

Can you give some example?

Tamil

[lgaorcl]

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;

[slimdave]

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?

[slimdave]

"to_char(dt.full_date, 'yyyymmdd')" should not be needed, btw.

[lgaorcl]

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.

[marist89]

Originally posted by slimdave
"to_char(dt.full_date, 'yyyymmdd')" should not be needed, btw.

LMAOPMP!!!!

[slimdave]

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?

[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.

[slimdave]

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 ...

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?