This is an old revision of the document!
Overview | Design | Creation | Indexing | Optimization
A rollup table is quite easy to create. The rollup tables are first created in the Omnidex Environment File, complete with the SQL statement that represents the rollup data. Then the UPDATE ROLLUPS statement is issued in ODXSQL to populate the rollup tables. Here is a simple example of creating a rollup that provides counts of people based on geographic regions:
CREATE TABLE "LIST_GEO" TYPE ROLLUP physical "dat/list.geo" AS "select COUNTRY, REGION, STATE, COUNTY, CITY, ZIP, MSA_CSA, PMSA, count(*) NUM_INDIVIDUALS from LIST group by COUNTRY, REGION, STATE, COUNTY, CITY, ZIP, MSA_CSA, PMSA"
IMPORTANT: Note that the aggregation has a column alias of NUM_INDIVIDUALS. It is required that all aggregation functions have column aliases as the alias becomes the column name in the table.
In ODXSQL, ODBC or JDBC, the following statement will populate the rollup tables in the Omnidex Environment:
update rollups