Can you create a multiple-table aggregate materialized views

Can you create a materialized view on mulitple tables and using aggregates?
I have created the logs on each table with rowid and including new values.

This is the create statement:
1 create materialized view test_sds
2 parallel
3 build immediate
4 refresh fast on commit
5 as
6 SELECT TO_CHAR(BILLPLED_POSTED_DATE,'MM') MONTH,RPAD(AIRLTABL_CODE_ICAO_2, 5) || ' - ' || AIRLTABL_DESCR AIRLINE
7 , SUM(Getledgervalue(AIRLTABL_CODE_ICAO_2
8 , BILLREBT_CODE
9 , BILLPLED_POSTED_DATE
10 , BILLPLED_POSTED_DATE)) VALUE
11 , BILLREBT_GROUP_SUMMARY
12 FROM AIRLTABL, BILLREBT, BILLPLED
13 WHERE AIRLTABL_CODE_ICAO_2 = BILLPLED_OPERATOR
14 ---AND BILLPLED_ACTUAL_DATE BETWEEN datFrom AND datTo
15 AND BILLREBT_GROUP_SUMMARY IS NOT NULL
16 AND BILLPLED_BILLCHGCODE_CODE = BILLREBT_CODE
17 AND BILLPLED_CREDIT > 0
18* GROUP BY TO_CHAR(BILLPLED_POSTED_DATE,'MM'),RPAD(AIRLTABL_CODE_ICAO_2, 5) || ' - ' || AIRLTABL_DESCR,BILLREBT_GROUP_SUMMARY

and this is the error:
FROM AIRLTABL, BILLREBT, BILLPLED
*
ERROR at line 12:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

If not is this achievable in Oracle 9i?
Thanks.

Code:

---


Restrictions on Fast Refresh on Materialized Views with Aggregates
Defining queries for materialized views with joins and aggregates have the following restrictions on fast refresh:

All restrictions from "General Restrictions on Fast Refresh".
Fast refresh is supported for both ON COMMIT and ON DEMAND materialized views, however the following restrictions apply:

All tables in the materialized view must have materialized view logs, and the materialized view logs must:
-----> Contain all columns from the table referenced in the materialized view.
Specify with ROWID and INCLUDING NEW VALUES.
Specify the SEQUENCE clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates.
Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for fast refresh.
COUNT(*) must be specified.
For each aggregate AGG(expr), the corresponding COUNT(expr) must be present.
If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and SUM(expr) must be specified. Oracle recommends that SUM(expr *expr) be specified. See Table 8-1 for further details.
The SELECT list must contain all GROUP BY columns.
If the materialized view has one of the following, then fast refresh is supported only on conventional DML inserts and direct loads.
Materialized views with MIN or MAX aggregates
Materialized views which have SUM(expr) but no COUNT(expr)
Materialized views without COUNT(*)
Such a materialized view is called an insert-only materialized view.

The COMPATIBILITY parameter must be set to 9.0 if the materialized aggregate view has inline views, outer joins, self joins or grouping sets and FAST REFRESH is specified during creation. Note that all other requirements for fast refresh specified previously must also be satisfied.
Materialized views with named views or subqueries in the FROM clause can be fast refreshed provided the views can be completely merged. For information on which views will merge, refer to the Oracle9i Database Performance Tuning Guide and Reference.
If there are no outer joins, you may have arbitrary selections and joins in the WHERE clause.
Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified. Also, unique constraints must exist on the join columns of the inner join table. If there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.
For materialized views with CUBE, ROLLUP, Grouping Sets, or concatenation of them, the following restrictions apply:
The SELECT list should contain grouping distinguisher that can either be a GROUPING_ID function on all GROUP BY expressions or GROUPING functions one for each GROUP BY expression. For example, if the GROUP BY clause of the materialized view is "GROUP BY CUBE(a, b)", then the SELECT list should contain either "GROUPING_ID(a, b)" or "GROUPING(a) AND GROUPING(b)" for the materialized view to be fast refreshable.
GROUP BY should not result in any duplicate groupings. For example, "GROUP BY a, ROLLUP(a, b)" is not fast refreshable because it results in duplicate groupings "(a), (a, b), AND (a)".

---

I had a big argument with Oracle Support over this.

There are examples of doing exactly this sort of thing in the Data Warehousing guide, but they said that they couldn't help me with my problem (which was actually to do with getting a fast refresh) because this was not a supported methodology.

Anyway, as pando points out there are some severe restrictions that you have to respect and which depend on your version number. I would wonder whether that user-defined function might be a problem, myslef.