How create a left outer join?

[cswords]

I am trying to display a report showing the number of transactions processed per time interval (i.e. hour, 1/2 hour, 15 minutes). The report must show ALL intervals, even if there are no transactions processed during that time interval.

My data table has the transaction id/processing time, as follows:

Table TXN_DATA
---------------
ID VARCHAR2(10) (i.e. ID1, ID2, ID3, ID4)
TIME TIMESTAMP(3) (i.e. 9:35, 9:40, 10:55)

To create the report, I thought I could create another table containing the start/end time of all possible intervals, as follows:

Table TIME_INTERVAL
-------------------
START TIMESTAMP(3) (i.e. 9:30, 10, 10:;30)
END TIMESTAMP(3) (i.e. 10, 10:30, 11)

Then, do a left outer join, i.e.

select d.id
from TXN_DATA d,
TIME_INTERVAL i
where
i.start <= d.time (+) AND
i.end > d.time (+)

Any thoughts? If the outer join would work, the problem is I don't know how to create a left outer join in Crystal Reports!

I am using Oracle 9i and Crystal Reports 8.5.

Thanks!

 

[Madawc]

Check Database Expert under Database on the menu. This gives you a picture of the links. Click on the relevant link and then choose 'link options'.

Madawc Williams (East Anglia, UK)