Crystal Report Tips

Tuesday, April 18, 2006

An alternative to Outer Joins

In Crystal Reports there is the option to link tables using outer joins. This allows you to show data from one table even though there is no corresponding data in another table. On large complex databases this can somtimes cause performance issues because a full table scan will be performed on the outer join table. This can be overcome by creating a view that always has a corresponding record and the outer join table is replaced by a field or fields that return null if there is no corresponding report.

An example would be:
create or replace view outer_join_vw (field1, field2) as
select field1, (select field2 from tableB B where A.field1 = B.field1)
from tableA A


Post a Comment

<< Home