Composite Reporting

[Dianne]

Using Oracle 9i and Oracle Discoverer or Oracle Reports (or other
tool) is it possible to create a single composite report that combines
records from multiple tables when each table has a different number
and type of fields? For instance, can you create a report that looks
like this:

TimeOfDay:xxx TableNumber:88 Field1:12.34 Field2:ABC Field3:698
TimeOfDay:xxx TableNumber:61 Field1:ABC
TimeOfDay:xxx TableNumber:22 Field1:47 Field2:9847
TimeOfDay:xxx TableNumber:88 Field1:4.56 Field2:DEF Field3:29
....

This example is pulling records from three different tables (#88, #61
and #22) and then sorting the results by TimeOfDay.

[Bricklen]

Dianne wrote:
Using Oracle 9i and Oracle Discoverer or Oracle Reports (or other tool) is it possible to create a single composite report that combines records from multiple tables when each table has a different number and type of fields? For instance, can you create a report that looks like this: TimeOfDay:xxx TableNumber:88 Field1:12.34 Field2:ABC Field3:698 TimeOfDay:xxx TableNumber:61 Field1:ABC TimeOfDay:xxx TableNumber:22 Field1:47 Field2:9847 TimeOfDay:xxx TableNumber:88 Field1:4.56 Field2:DEF Field3:29 ... This example is pulling records from three different tables (#88, #61 and #22) and then sorting the results by TimeOfDay.

maybe something like this (using UNION or UNION ALL)

select TimeOfDay,TableNumber,Field1,Field2,NULL Field3
from table_22
union
select TimeOfDay,TableNumber,NULL Field1,NULL Field2,Field3
from table_61
union
select TimeOfDay,TableNumber,Field1,Field2,Field3
from table_88
order by TimeOfDay;