Quite a while ago I asked a question in this forum about an unusual join and subtraction problem (
1103890 ). I got what appeared to be a working query. Until now, I haven't been able to really utilize this. Now that I'm looking closer at the results, I think there's something wrong with it, but I can't figure out why it's happening.
Using the table definition from the original question, the following simple query shows a particular excerpt of the data I'm looking at.
select start_time, request_id, event_type, code_range, duration from MYTABLE where REQUEST_ID='abc'
This results in the following rows (columns separated with "/"):
START_TIME/REQUEST_ID/EVENT_TYPE/CODE_RANGE/DURATION
2010-11-12 01:42:04.0/abc/Junk/publicEntryPoint/2,003
2010-11-12 01:42:04.0/abc/Junk/webServiceCall/947
2010-11-12 01:42:04.0/abc/Junk/webServiceCall/969
Another similar query with REQUEST_ID='def' results in this:
START_TIME/REQUEST_ID/EVENT_TYPE/CODE_RANGE/DURATION
2010-11-12 00:22:13.0/def/junk/webServiceCall/788
2010-11-12 00:22:13.0/def/junk/webServiceCall/1,128
2010-11-12 00:22:13.0/def/junk/publicEntryPoint/2,003
The following is a simplified excerpt of the query I'm having trouble with:
select start_time, request_id, event_type, code_range, duration, case code_range
when 'publicEntryPoint' then duration * 2 - sum(duration) over(partition by request_id)
else -1
end inner_duration from MYTABLE where EVENT_TYPE='junk' and trunc(START_TIME) = to_date('2010-11-12','yyyy-mm-dd')
and rownum < 1000;
Notice the couple of unconventional features used here, the "case/when" and "sum() over(partition)".
This returns a bunch of rows (count: 999), but here are the two with the particular REQUEST_ID values:
START_TIME/REQUEST_ID/EVENT_TYPE/CODE_RANGE/DURATION/INNER_DURATION
2010-11-12 01:42:04.0/abc/junk/publicEntryPoint/2,003/2,003
2010-11-12 00:22:13.0/def/junk/publicEntryPoint/2,003/87
The second row is correct. The first row has an unexpected INNER_DURATION value of 2003. It should be 87 like the second row. I don't understand why this is happening.