I have following derby sql statement:
SELECT DISTINCT MEETINGS.MEETING_ID,
MEETINGS.TITLE,
RECORDING_FRAME.STARTTIME,
RECORDING_FRAME.ENDTIME,
{
fn timestampdiff(SQL_TSI_FRAC_SECOND,
RECORDING_FRAME.STARTTIME,
RECORDING_FRAME.ENDTIME)
} AS RUNTIMEFIELD_SESSIONDURATION
FROM APP.MEETINGS
JOIN APP.RECORDING ON
MEETINGS.MEETING_ID=RECORDING.MEETINGID
JOIN APP.RECORDING_TRACK ON
RECORDING.RECORDING_ID=RECORDING_TRACK.RECORDING_ID
JOIN APP.RECORDING_FRAME ON
RECORDING.RECORDING_ID=RECORDING_FRAME.RECORDING_ID
WHERE RECORDING_FRAME.MODULE LIKE 'Recorder' AND
MEETINGS.STATUS LIKE 'ENDED'
If I execute this query, I get following results:
MEETING_ID TITLE STARTTIME ENDTIME RUNTIMEFIELD_SESSIONDURATION
---------- -------------------- ------------------- ------------------- ----------------------------
65536 abc 2013-03-06 21:28:49 (null) (null)
65536 abc (null) 2013-03-06 21:30:04 (null)
65537 fasfasf 2013-03-06 21:13:13 (null) (null)
65537 fasfasf (null) 2013-03-06 21:13:40 (null)
360448 fasfffa 2013-03-06 21:31:07 (null) (null)
360448 fasfffa (null) 2013-03-06 21:38:13 (null)
360448 fasfffa (null) (null) (null)
360450 test 2013-03-06 22:07:31 (null) (null)
360450 test (null) 2013-03-06 22:14:13 (null)
1605632 test benno 2013-03-19 18:21:03 (null) (null)
1605632 test benno (null) 2013-03-19 18:28:02 (null)
1605632 test benno (null) (null) (null)
3211281 Conference Unni 2013-03-28 11:03:34 (null) (null)
3211281 Conference Unni (null) 2013-03-28 11:12:49 (null)
3211281 Conference Unni (null) (null) (null)
3211283 test 2013-03-28 10:51:27 (null) (null)
3211283 test (null) 2013-03-28 10:59:13 (null)
3211283 test (null) (null) (null)
3211284 test (Copy) 2013-03-28 11:02:57 (null) (null)
3211284 test (Copy) (null) 2013-03-28 11:03:08 (null)
3211285 test (Copy) (Kopie) 2013-03-28 18:24:35 (null) (null)
3211285 test (Copy) (Kopie) (null) 2013-03-28 18:24:58 (null)
3211288 ss test 2013-03-28 17:02:20 (null) (null)
3211288 ss test (null) 2013-03-28 17:34:33 (null)
3211288 ss test (null) (null) (null)
Now, it is obvious that same event (for example, event "abc") from this listing has two records, starttime and endtime. How do I modify this derby sql statement, so it will return for event "abc" starttime and endtime in single row?
The way I see it, it seems that table RECORDING_FRAME has 2 or 3 rows that are joined with RECORDING_TRACK on RECORDING_ID. One row has starttime not null, but endtime null, one row has starttime null but endtime not null, and some recordings also have a third row with starttime and endtime null. What you need to do is convert the union (multiple rows) to a join (single row). Here's how you do it:
First create a view of RECORDING_FRAME that contains only the rows where starttime is not null.
Create view APP.VW_RF_START as
(Select RECORDING_ID, STARTTIME from RECORDING_FRAME
where STARTTIME is not null)
Now create a view that contains only the records of RECORDING_FRAME where endtime is not null.
Create view APP.VW_RF_END as
(Select RECORDING_ID, STARTTIME from RECORDING_FRAME
where ENDTIME is not null)
Now if you join the two views on recording_id, you have starttime and endtime in the same row. And your query is:
SELECT DISTINCT
MEETINGS.MEETING_ID, MEETINGS.TITLE,
VW_RF_START.STARTTIME, VW_RF_END.STARTTIME,
{
fn timestampdiff(SQL_TSI_FRAC_SECOND,
RECORDING_FRAME.STARTTIME,
RECORDING_FRAME.ENDTIME)
} AS RUNTIMEFIELD_SESSIONDURATION
FROM APP.MEETINGS
JOIN APP.RECORDING ON
MEETINGS.MEETING_ID=RECORDING.MEETINGID
JOIN APP.RECORDING_TRACK ON
RECORDING.RECORDING_ID=RECORDING_TRACK.RECORDING_ID
JOIN APP.VW_RF_START ON
RECORDING.RECORDING_ID=VW_RF_START.RECORDING_ID
JOIN APP.VW_RF_END ON
RECORDING.RECORDING_ID=VW_RF_START.RECORDING_ID
WHERE RECORDING_FRAME.MODULE LIKE 'Recorder' AND MEETINGS.STATUS LIKE 'ENDED'
If you can't create the views, then you can define them in the query:
SELECT DISTINCT
MEETINGS.MEETING_ID, MEETINGS.TITLE,
VW_RF_START.STARTTIME, VW_RF_END.STARTTIME,
{
fn timestampdiff(SQL_TSI_FRAC_SECOND,
RECORDING_FRAME.STARTTIME,
RECORDING_FRAME.ENDTIME)
} AS RUNTIMEFIELD_SESSIONDURATION
FROM APP.MEETINGS
APP.RECORDING ON
MEETINGS.MEETING_ID=RECORDING.MEETINGID
JOIN APP.RECORDING_TRACK ON
RECORDING.RECORDING_ID=RECORDING_TRACK.RECORDING_ID
JOIN
(Select RECORDING_ID, STARTTIME from RECORDING_FRAME
where ENDTIME is not null) as VW_RF_START
ON
RECORDING.RECORDING_ID=VW_RF_START.RECORDING_ID
JOIN
(Select RECORDING_ID, STARTTIME from RECORDING_FRAME
where ENDTIME is not null) as APP.VW_RF_END
ON RECORDING.RECORDING_ID=VW_RF_START.RECORDING_ID
WHERE RECORDING_FRAME.MODULE LIKE 'Recorder' AND
MEETINGS.STATUS LIKE 'ENDED'