Oracle 10g : MODEL Clause

The following query gets the sum of units and sum of revenue from all regions and subtracts that from the Worldwide units and revenue to calculate a "rest of world" value for units and revenue. geography_id = -1 is the id for worldwide.

You can write a standard query like this :

select *
from (select ar.model_id,
ar.period_id,
(nvl(ww.units, 0) - ar.sum_units) sum_units_row,
(nvl(ww.revenue, 0) - ar.sum_rev) sum_rev_row
from (select model_id,
period_id,
sum(md.units) sum_units,
sum(md.revenue) sum_rev
from facttable md
where md.geography_id <> -1
group by model_id, period_id) ar
left join (select model_id,
geography_id,
period_id,
md.units units,
md.revenue revenue
from facttable md
where md.geography_id = -1) ww on ar.model_id =
ww.model_id
and ar.period_id =
ww.period_id)
where sum_units_row < 5 =" units"> -1, revenue - 5 = revenue - 1 - sum(revenue) geography_id > -1))
where units < goal =" ALL_ROWS" cost="15" cardinality="4655" bytes="242060" owner="WK" cost="15" cardinality="4655" bytes="242060" cardinality="4655" bytes="111720" owner="WK" name="FACTTABLE" cost="15" cardinality="4655" bytes="111720" href="http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14223/sqlmodel.htm#DWHSG022%20">Oracle Model Clause

More Examples :

http://www.oracle.com/technology/products/bi/db/10g/model_examples.html

http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_sql.html

Long live "Tom Kyte".

Good Luck !!
r-a-v-i