>>obey TEST002(set_up);
>>----------------------------------------------------
>>-------------------- set up section ----------------
>>----------------------------------------------------
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>control query default MVQR_REWRITE_ENABLED_OPTION 'ON';

--- SQL operation complete.
>>control query default QUERY_CACHE '0';

--- SQL operation complete.
>>--control query default MVQR_PUBLISH_TO 'PRIVATE';
>>--control query default MULTI_JOIN_THRESHOLD '2';
>>
>>prepare checkPlan from
+>  select operator, tname
+>  from table(explain(NULL, 'QUERYSTMT'))
+>  where operator like '%_SCAN%'
+>  order by tname;

--- SQL command prepared.
>>
>>obey GetMvDesc;
>>--set pattern $$VERS_CURR_SCHEMA_VERSION$$ 2400;
>>set param ?mvDescSubId  -2;
>>set param ?mvName 'SUMBY_DAY';
>>set param ?mvSchemaName 'SUMBY';
>>
>>-- query the TEXT SMD table for the descriptor of an MV
>>-- Note: Cast to UTF8, but we don't want to exceed the 20000 byte line
>>--       length limit of the NT version of awk (file CropDescriptor.ksh).
>>prepare getMVDescFromTEXT from
+>  select cast(text0 || coalesce(text1, '') || coalesce(text2, '') || coalesce(text3, '')
+>                    || coalesce(text4, '') || coalesce(text5, '') || coalesce(text6, '')
+>                    || coalesce(text7, '') || coalesce(text8, '') as char(15000 bytes) character set utf8)  as mv_descriptor_text
+>  from
+>		 (SELECT o.object_uid as uid0, text as text0
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 0 ) as row0
+>	left outer join
+>		 (SELECT o.object_uid as uid1, text as text1
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 1 ) as row1
+>		 on uid0 = uid1
+>	left outer join
+>		 (SELECT o.object_uid as uid2, text as text2
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 2 ) as row2
+>		 on uid0 = uid2
+>	left outer join
+>		 (SELECT o.object_uid as uid3, text as text3
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 3 ) as row3
+>		 on uid0 = uid3
+>	left outer join
+>		 (SELECT o.object_uid as uid4, text as text4
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 4 ) as row4
+>		 on uid0 = uid4
+>	left outer join
+>		 (SELECT o.object_uid as uid5, text as text5
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 5 ) as row5
+>		 on uid0 = uid5
+>	left outer join
+>		 (SELECT o.object_uid as uid6, text as text6
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 6 ) as row6
+>		 on uid0 = uid6
+>	left outer join
+>		 (SELECT o.object_uid as uid7, text as text7
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 7 ) as row7
+>		 on uid0 = uid7
+>	left outer join
+>		 (SELECT o.object_uid as uid8, text as text8
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 8 ) as row8
+>		 on uid0 = uid8;

--- SQL command prepared.
>>
>>
>>create schema sumby;

--- SQL operation complete.
>>set schema sumby;

--- SQL operation complete.
>>set pattern $$MVSchemaName$$ SUMBY;
>>
>>create table T002_FACT (
+>	fday   int,  -- 1-31
+>	fmonth int,  -- 1-12
+>	fyear  int,  -- 4 digit year
+>	fitem  int,
+>	fdep   int,
+>	fstore int,
+>	fstate int,
+>	fprice numeric (8,2),
+>	dimkey int) no partition;

--- SQL operation complete.
>>
>>create table T002_DIM (dkey int, dimdata int) no partition;

--- SQL operation complete.
>>
>>insert into T002_FACT values
+> ( 4, 1,2010,4,4,3,4,83,8),
+> (23, 1,2010,2,5,5,4,67,3),
+> (11, 3,2010,1,1,5,4,39,6),
+> ( 8, 4,2010,4,3,4,2,56,5),
+> (16, 4,2010,3,1,5,2,67,9),
+> ( 4, 5,2010,2,4,2,4,40,2),
+> ( 6, 7,2010,3,2,4,1,63,6),
+> ( 8, 7,2010,5,3,2,4,90,3),
+> (19, 7,2010,3,3,4,2,50,8),
+> ( 1, 8,2010,3,1,4,3, 4,3),
+> ( 2, 9,2010,3,1,3,4, 8,1),
+> ( 4, 9,2010,2,3,1,5,62,8),
+> (15, 9,2010,5,2,4,3,45,4),
+> (31,10,2010,2,4,2,1,39,8),
+> (12,11,2010,4,3,5,5,27,9),
+> ( 1,12,2010,3,2,2,5,21,8),
+> (10,12,2010,3,2,4,5,31,1),
+> (22, 1,2011,5,5,3,4,12,2),
+> ( 7, 6,2011,1,4,1,4,73,5),
+> (10, 6,2011,5,5,3,2,61,1),
+> (16, 6,2011,1,5,5,4,76,4),
+> (31, 6,2011,3,4,3,2,66,7),
+> (22, 7,2011,4,5,2,1,15,3),
+> (25, 7,2011,5,4,3,5,75,7),
+> ( 5, 8,2011,1,4,1,4,87,3),
+> (10,10,2011,4,1,4,1,66,6),
+> (24,11,2011,4,1,3,3,88,8),
+> (13,12,2011,1,3,3,1,59,7),
+> (15,12,2011,3,3,4,3,95,5),
+> (25,12,2011,2,2,1,3,69,4),
+> ( 9, 1,2012,2,5,4,2,71,9),
+> ( 1, 2,2012,2,1,2,1,49,6),
+> ( 9, 2,2012,4,4,5,3,65,6),
+> ( 9, 2,2012,3,2,1,3,67,3),
+> (17, 2,2012,2,2,3,3,98,3),
+> ( 1, 3,2012,2,1,2,4,55,7),
+> (10, 3,2012,5,1,5,4,11,8),
+> (28, 3,2012,1,3,2,5, 7,5),
+> (21, 4,2012,1,2,1,3,32,8),
+> (23, 4,2012,4,2,2,1,25,4),
+> (27, 4,2012,4,5,4,2,61,5),
+> (28, 5,2012,5,5,5,1,44,8),
+> ( 2, 6,2012,4,3,2,3,31,2),
+> (21, 6,2012,4,3,5,3,70,5),
+> (29, 6,2012,5,2,5,1,51,5),
+> ( 9, 7,2012,1,4,5,5,14,4),
+> (29, 7,2012,2,1,5,3,53,5),
+> (21, 8,2012,3,4,4,1,58,1),
+> (18,12,2012,2,1,2,3,20,1),
+> (20,12,2012,5,1,2,1, 4,6);

--- 50 row(s) inserted.
>>
>>insert into T002_DIM values
+> (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9);

--- 9 row(s) inserted.
>>
>>obey TEST002(create_mvs);
>>--===========================================
>>-- MJV
>>--===========================================
>>
>>create mv sumby_none
+>	refresh on request
+>	initialized on create
+>	as  select *
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_NONE_666737858_8992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_NONE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_NONE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873637470061' isKeyCovered='1' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873636735743' isKeyCovered='1' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J209'>
          <Column id='C175' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column ref='C163'>
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O182' name='SYS_SYSKEY1'>
        <Column id='C154' tableId='T2' colIndex='0' isNullable='0'>
          CAT.SUMBY.T002_FACT.SYSKEY
        </Column>
      </Output>
      <Output id='O185' name='FDAY'>
        <Column id='C155' tableId='T2' colIndex='1'>
          CAT.SUMBY.T002_FACT.FDAY
        </Column>
      </Output>
      <Output id='O188' name='FMONTH'>
        <Column id='C156' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
      </Output>
      <Output id='O191' name='FYEAR'>
        <Column id='C157' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
      </Output>
      <Output id='O194' name='FITEM'>
        <Column id='C158' tableId='T2' colIndex='4'>
          CAT.SUMBY.T002_FACT.FITEM
        </Column>
      </Output>
      <Output id='O197' name='FDEP'>
        <Column id='C159' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
      </Output>
      <Output id='O200' name='FSTORE'>
        <Column id='C160' tableId='T2' colIndex='6'>
          CAT.SUMBY.T002_FACT.FSTORE
        </Column>
      </Output>
      <Output id='O203' name='FSTATE'>
        <Column id='C161' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Output>
      <Output id='O206' name='FPRICE'>
        <Column id='C162' tableId='T2' colIndex='8'>
          CAT.SUMBY.T002_FACT.FPRICE
        </Column>
      </Output>
      <Output id='O209' name='DIMKEY'>
        <Column id='C163' tableId='T2' colIndex='9'>
          CAT.SUMBY.T002_FACT.DIMKEY
        </Column>
      </Output>
      <Output id='O212' name='SYS_SYSKEY2'>
        <Column id='C174' tableId='T1' colIndex='0' isNullable='0'>
          CAT.SUMBY.T002_DIM.SYSKEY
        </Column>
      </Output>
      <Output id='O218' name='DIMDATA'>
        <Column id='C176' tableId='T1' colIndex='2'>
          CAT.SUMBY.T002_DIM.DIMDATA
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>--===========================================
>>-- Single dimention
>>--===========================================
>>
>>create mv sumby_day
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fday oday, fmonth omonth, fyear oyear
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fday, fmonth, fyear;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_DAY_611666688_8992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_DAY;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_DAY
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J180'>
          <Column id='C140' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C128' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O146' name='TOTAL_PRICE'>
        <Expr id='X146'>
          <Function id='S146' op='sum' aggregateFunc='3'>
            <Column id='C127' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O147' name='SUMDATA'>
        <Expr id='X147'>
          <Function id='S147' op='sum' aggregateFunc='3'>
            <Column id='C141' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O149' name='SYS_COUNTSTAR1'>
        <Expr id='X149'>
          <Function id='S149' op='count' aggregateFunc='1'>
            <NumericVal id='S148' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O150' name='SYS_COUNT2'>
        <Expr id='X150'>
          <Function id='S150' op='count_nonull' aggregateFunc='2'>
            <Column ref='C127'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O151' name='SYS_COUNT3'>
        <Expr id='X151'>
          <Function id='S151' op='count_nonull' aggregateFunc='2'>
            <Column ref='C141'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O156' name='ODAY'>
        <Column ref='C120'>
        </Column>
      </Output>
      <Output id='O159' name='OMONTH'>
        <Column ref='C121'>
        </Column>
      </Output>
      <Output id='O162' name='OYEAR'>
        <Column ref='C122'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C120' tableId='T2' colIndex='1'>
          CAT.SUMBY.T002_FACT.FDAY
        </Column>
        <Column id='C121' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
        <Column id='C122' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_month
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fmonth omonth, fyear oyear
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fmonth, fyear;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_MONTH_176592698_8992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_MONTH;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_MONTH
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J177'>
          <Column id='C137' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C125' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O143' name='TOTAL_PRICE'>
        <Expr id='X143'>
          <Function id='S143' op='sum' aggregateFunc='3'>
            <Column id='C124' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O144' name='SUMDATA'>
        <Expr id='X144'>
          <Function id='S144' op='sum' aggregateFunc='3'>
            <Column id='C138' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O146' name='SYS_COUNTSTAR1'>
        <Expr id='X146'>
          <Function id='S146' op='count' aggregateFunc='1'>
            <NumericVal id='S145' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O147' name='SYS_COUNT2'>
        <Expr id='X147'>
          <Function id='S147' op='count_nonull' aggregateFunc='2'>
            <Column ref='C124'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O148' name='SYS_COUNT3'>
        <Expr id='X148'>
          <Function id='S148' op='count_nonull' aggregateFunc='2'>
            <Column ref='C138'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O156' name='OMONTH'>
        <Column ref='C118'>
        </Column>
      </Output>
      <Output id='O159' name='OYEAR'>
        <Column ref='C119'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C118' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
        <Column id='C119' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_year
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fyear oyear
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fyear;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_YEAR_527437519_8992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_YEAR;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_YEAR
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J174'>
          <Column id='C134' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C122' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O140' name='TOTAL_PRICE'>
        <Expr id='X140'>
          <Function id='S140' op='sum' aggregateFunc='3'>
            <Column id='C121' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O141' name='SUMDATA'>
        <Expr id='X141'>
          <Function id='S141' op='sum' aggregateFunc='3'>
            <Column id='C135' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O143' name='SYS_COUNTSTAR1'>
        <Expr id='X143'>
          <Function id='S143' op='count' aggregateFunc='1'>
            <NumericVal id='S142' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O144' name='SYS_COUNT2'>
        <Expr id='X144'>
          <Function id='S144' op='count_nonull' aggregateFunc='2'>
            <Column ref='C121'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O145' name='SYS_COUNT3'>
        <Expr id='X145'>
          <Function id='S145' op='count_nonull' aggregateFunc='2'>
            <Column ref='C135'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O156' name='OYEAR'>
        <Column ref='C116'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C116' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_item
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fitem oitem, fdep odep
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fitem, fdep;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_ITEM_211728429_8992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_ITEM;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_ITEM
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J177'>
          <Column id='C137' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C125' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O143' name='TOTAL_PRICE'>
        <Expr id='X143'>
          <Function id='S143' op='sum' aggregateFunc='3'>
            <Column id='C124' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O144' name='SUMDATA'>
        <Expr id='X144'>
          <Function id='S144' op='sum' aggregateFunc='3'>
            <Column id='C138' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O146' name='SYS_COUNTSTAR1'>
        <Expr id='X146'>
          <Function id='S146' op='count' aggregateFunc='1'>
            <NumericVal id='S145' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O147' name='SYS_COUNT2'>
        <Expr id='X147'>
          <Function id='S147' op='count_nonull' aggregateFunc='2'>
            <Column ref='C124'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O148' name='SYS_COUNT3'>
        <Expr id='X148'>
          <Function id='S148' op='count_nonull' aggregateFunc='2'>
            <Column ref='C138'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O162' name='OITEM'>
        <Column ref='C120'>
        </Column>
      </Output>
      <Output id='O165' name='ODEP'>
        <Column ref='C121'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C120' tableId='T2' colIndex='4'>
          CAT.SUMBY.T002_FACT.FITEM
        </Column>
        <Column id='C121' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_dep
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fdep odep
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fdep;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_DEP_677743539_8992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_DEP;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_DEP
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J174'>
          <Column id='C134' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C122' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O140' name='TOTAL_PRICE'>
        <Expr id='X140'>
          <Function id='S140' op='sum' aggregateFunc='3'>
            <Column id='C121' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O141' name='SUMDATA'>
        <Expr id='X141'>
          <Function id='S141' op='sum' aggregateFunc='3'>
            <Column id='C135' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O143' name='SYS_COUNTSTAR1'>
        <Expr id='X143'>
          <Function id='S143' op='count' aggregateFunc='1'>
            <NumericVal id='S142' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O144' name='SYS_COUNT2'>
        <Expr id='X144'>
          <Function id='S144' op='count_nonull' aggregateFunc='2'>
            <Column ref='C121'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O145' name='SYS_COUNT3'>
        <Expr id='X145'>
          <Function id='S145' op='count_nonull' aggregateFunc='2'>
            <Column ref='C135'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O162' name='ODEP'>
        <Column ref='C118'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C118' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_store
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fstore ostore, fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fstore, fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_STORE_671448449_8992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_STORE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_STORE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J177'>
          <Column id='C137' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C125' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O143' name='TOTAL_PRICE'>
        <Expr id='X143'>
          <Function id='S143' op='sum' aggregateFunc='3'>
            <Column id='C124' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O144' name='SUMDATA'>
        <Expr id='X144'>
          <Function id='S144' op='sum' aggregateFunc='3'>
            <Column id='C138' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O146' name='SYS_COUNTSTAR1'>
        <Expr id='X146'>
          <Function id='S146' op='count' aggregateFunc='1'>
            <NumericVal id='S145' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O147' name='SYS_COUNT2'>
        <Expr id='X147'>
          <Function id='S147' op='count_nonull' aggregateFunc='2'>
            <Column ref='C124'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O148' name='SYS_COUNT3'>
        <Expr id='X148'>
          <Function id='S148' op='count_nonull' aggregateFunc='2'>
            <Column ref='C138'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O168' name='OSTORE'>
        <Column ref='C122'>
        </Column>
      </Output>
      <Output id='O171' name='OSTATE'>
        <Column ref='C123'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C122' tableId='T2' colIndex='6'>
          CAT.SUMBY.T002_FACT.FSTORE
        </Column>
        <Column id='C123' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_state
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_STATE_636638459_8992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_STATE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_STATE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J174'>
          <Column id='C134' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C122' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O140' name='TOTAL_PRICE'>
        <Expr id='X140'>
          <Function id='S140' op='sum' aggregateFunc='3'>
            <Column id='C121' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O141' name='SUMDATA'>
        <Expr id='X141'>
          <Function id='S141' op='sum' aggregateFunc='3'>
            <Column id='C135' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O143' name='SYS_COUNTSTAR1'>
        <Expr id='X143'>
          <Function id='S143' op='count' aggregateFunc='1'>
            <NumericVal id='S142' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O144' name='SYS_COUNT2'>
        <Expr id='X144'>
          <Function id='S144' op='count_nonull' aggregateFunc='2'>
            <Column ref='C121'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O145' name='SYS_COUNT3'>
        <Expr id='X145'>
          <Function id='S145' op='count_nonull' aggregateFunc='2'>
            <Column ref='C135'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O168' name='OSTATE'>
        <Column ref='C120'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C120' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>--===========================================
>>-- Two dimentions
>>--===========================================
>>
>>create mv sumby_day_item
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fday oday, fmonth omonth, fyear oyear, fitem oitem, fdep odep
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fday, fmonth, fyear, fitem, fdep;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_DAY_ITEM_897514669_8992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_DAY_ITEM;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_DAY_ITEM
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J186'>
          <Column id='C146' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C134' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O152' name='TOTAL_PRICE'>
        <Expr id='X152'>
          <Function id='S152' op='sum' aggregateFunc='3'>
            <Column id='C133' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O153' name='SUMDATA'>
        <Expr id='X153'>
          <Function id='S153' op='sum' aggregateFunc='3'>
            <Column id='C147' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O155' name='SYS_COUNTSTAR1'>
        <Expr id='X155'>
          <Function id='S155' op='count' aggregateFunc='1'>
            <NumericVal id='S154' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O156' name='SYS_COUNT2'>
        <Expr id='X156'>
          <Function id='S156' op='count_nonull' aggregateFunc='2'>
            <Column ref='C133'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O157' name='SYS_COUNT3'>
        <Expr id='X157'>
          <Function id='S157' op='count_nonull' aggregateFunc='2'>
            <Column ref='C147'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O162' name='ODAY'>
        <Column ref='C126'>
        </Column>
      </Output>
      <Output id='O165' name='OMONTH'>
        <Column ref='C127'>
        </Column>
      </Output>
      <Output id='O168' name='OYEAR'>
        <Column ref='C128'>
        </Column>
      </Output>
      <Output id='O171' name='OITEM'>
        <Column ref='C129'>
        </Column>
      </Output>
      <Output id='O174' name='ODEP'>
        <Column ref='C130'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C126' tableId='T2' colIndex='1'>
          CAT.SUMBY.T002_FACT.FDAY
        </Column>
        <Column id='C127' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
        <Column id='C128' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C129' tableId='T2' colIndex='4'>
          CAT.SUMBY.T002_FACT.FITEM
        </Column>
        <Column id='C130' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_month_item
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fmonth omonth, fyear oyear, fitem oitem, fdep odep
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fmonth, fyear, fitem, fdep;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_MONTH_ITEM_457252879_8992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_MONTH_ITEM;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_MONTH_ITEM
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J183'>
          <Column id='C143' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C131' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O149' name='TOTAL_PRICE'>
        <Expr id='X149'>
          <Function id='S149' op='sum' aggregateFunc='3'>
            <Column id='C130' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O150' name='SUMDATA'>
        <Expr id='X150'>
          <Function id='S150' op='sum' aggregateFunc='3'>
            <Column id='C144' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O152' name='SYS_COUNTSTAR1'>
        <Expr id='X152'>
          <Function id='S152' op='count' aggregateFunc='1'>
            <NumericVal id='S151' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O153' name='SYS_COUNT2'>
        <Expr id='X153'>
          <Function id='S153' op='count_nonull' aggregateFunc='2'>
            <Column ref='C130'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O154' name='SYS_COUNT3'>
        <Expr id='X154'>
          <Function id='S154' op='count_nonull' aggregateFunc='2'>
            <Column ref='C144'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O162' name='OMONTH'>
        <Column ref='C124'>
        </Column>
      </Output>
      <Output id='O165' name='OYEAR'>
        <Column ref='C125'>
        </Column>
      </Output>
      <Output id='O168' name='OITEM'>
        <Column ref='C126'>
        </Column>
      </Output>
      <Output id='O171' name='ODEP'>
        <Column ref='C127'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C124' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
        <Column id='C125' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C126' tableId='T2' colIndex='4'>
          CAT.SUMBY.T002_FACT.FITEM
        </Column>
        <Column id='C127' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_year_item
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fyear oyear, fitem oitem, fdep odep
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fyear, fitem, fdep;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_YEAR_ITEM_543829889_8992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_YEAR_ITEM;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_YEAR_ITEM
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J180'>
          <Column id='C140' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C128' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O146' name='TOTAL_PRICE'>
        <Expr id='X146'>
          <Function id='S146' op='sum' aggregateFunc='3'>
            <Column id='C127' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O147' name='SUMDATA'>
        <Expr id='X147'>
          <Function id='S147' op='sum' aggregateFunc='3'>
            <Column id='C141' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O149' name='SYS_COUNTSTAR1'>
        <Expr id='X149'>
          <Function id='S149' op='count' aggregateFunc='1'>
            <NumericVal id='S148' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O150' name='SYS_COUNT2'>
        <Expr id='X150'>
          <Function id='S150' op='count_nonull' aggregateFunc='2'>
            <Column ref='C127'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O151' name='SYS_COUNT3'>
        <Expr id='X151'>
          <Function id='S151' op='count_nonull' aggregateFunc='2'>
            <Column ref='C141'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O162' name='OYEAR'>
        <Column ref='C122'>
        </Column>
      </Output>
      <Output id='O165' name='OITEM'>
        <Column ref='C123'>
        </Column>
      </Output>
      <Output id='O168' name='ODEP'>
        <Column ref='C124'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C122' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C123' tableId='T2' colIndex='4'>
          CAT.SUMBY.T002_FACT.FITEM
        </Column>
        <Column id='C124' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_day_store
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fday oday, fmonth omonth, fyear oyear, fstore ostore, fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fday, fmonth, fyear, fstore, fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_DAY_STORE_577636999_8992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_DAY_STORE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_DAY_STORE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J186'>
          <Column id='C146' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C134' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O152' name='TOTAL_PRICE'>
        <Expr id='X152'>
          <Function id='S152' op='sum' aggregateFunc='3'>
            <Column id='C133' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O153' name='SUMDATA'>
        <Expr id='X153'>
          <Function id='S153' op='sum' aggregateFunc='3'>
            <Column id='C147' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O155' name='SYS_COUNTSTAR1'>
        <Expr id='X155'>
          <Function id='S155' op='count' aggregateFunc='1'>
            <NumericVal id='S154' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O156' name='SYS_COUNT2'>
        <Expr id='X156'>
          <Function id='S156' op='count_nonull' aggregateFunc='2'>
            <Column ref='C133'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O157' name='SYS_COUNT3'>
        <Expr id='X157'>
          <Function id='S157' op='count_nonull' aggregateFunc='2'>
            <Column ref='C147'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O162' name='ODAY'>
        <Column ref='C126'>
        </Column>
      </Output>
      <Output id='O165' name='OMONTH'>
        <Column ref='C127'>
        </Column>
      </Output>
      <Output id='O168' name='OYEAR'>
        <Column ref='C128'>
        </Column>
      </Output>
      <Output id='O177' name='OSTORE'>
        <Column ref='C131'>
        </Column>
      </Output>
      <Output id='O180' name='OSTATE'>
        <Column ref='C132'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C126' tableId='T2' colIndex='1'>
          CAT.SUMBY.T002_FACT.FDAY
        </Column>
        <Column id='C127' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
        <Column id='C128' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C131' tableId='T2' colIndex='6'>
          CAT.SUMBY.T002_FACT.FSTORE
        </Column>
        <Column id='C132' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_month_store
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fmonth omonth, fyear oyear, fstore ostore, fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fmonth, fyear, fstore, fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_MONTH_STORE_514642121_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_MONTH_STORE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_MONTH_STORE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J183'>
          <Column id='C143' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C131' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O149' name='TOTAL_PRICE'>
        <Expr id='X149'>
          <Function id='S149' op='sum' aggregateFunc='3'>
            <Column id='C130' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O150' name='SUMDATA'>
        <Expr id='X150'>
          <Function id='S150' op='sum' aggregateFunc='3'>
            <Column id='C144' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O152' name='SYS_COUNTSTAR1'>
        <Expr id='X152'>
          <Function id='S152' op='count' aggregateFunc='1'>
            <NumericVal id='S151' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O153' name='SYS_COUNT2'>
        <Expr id='X153'>
          <Function id='S153' op='count_nonull' aggregateFunc='2'>
            <Column ref='C130'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O154' name='SYS_COUNT3'>
        <Expr id='X154'>
          <Function id='S154' op='count_nonull' aggregateFunc='2'>
            <Column ref='C144'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O162' name='OMONTH'>
        <Column ref='C124'>
        </Column>
      </Output>
      <Output id='O165' name='OYEAR'>
        <Column ref='C125'>
        </Column>
      </Output>
      <Output id='O174' name='OSTORE'>
        <Column ref='C128'>
        </Column>
      </Output>
      <Output id='O177' name='OSTATE'>
        <Column ref='C129'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C124' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
        <Column id='C125' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C128' tableId='T2' colIndex='6'>
          CAT.SUMBY.T002_FACT.FSTORE
        </Column>
        <Column id='C129' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_year_store
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fyear oyear, fstore ostore, fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fyear, fstore, fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_YEAR_STORE_138117231_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_YEAR_STORE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_YEAR_STORE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J180'>
          <Column id='C140' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C128' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O146' name='TOTAL_PRICE'>
        <Expr id='X146'>
          <Function id='S146' op='sum' aggregateFunc='3'>
            <Column id='C127' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O147' name='SUMDATA'>
        <Expr id='X147'>
          <Function id='S147' op='sum' aggregateFunc='3'>
            <Column id='C141' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O149' name='SYS_COUNTSTAR1'>
        <Expr id='X149'>
          <Function id='S149' op='count' aggregateFunc='1'>
            <NumericVal id='S148' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O150' name='SYS_COUNT2'>
        <Expr id='X150'>
          <Function id='S150' op='count_nonull' aggregateFunc='2'>
            <Column ref='C127'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O151' name='SYS_COUNT3'>
        <Expr id='X151'>
          <Function id='S151' op='count_nonull' aggregateFunc='2'>
            <Column ref='C141'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O162' name='OYEAR'>
        <Column ref='C122'>
        </Column>
      </Output>
      <Output id='O171' name='OSTORE'>
        <Column ref='C125'>
        </Column>
      </Output>
      <Output id='O174' name='OSTATE'>
        <Column ref='C126'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C122' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C125' tableId='T2' colIndex='6'>
          CAT.SUMBY.T002_FACT.FSTORE
        </Column>
        <Column id='C126' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>--===========================================
>>
>>create mv sumby_day_dep
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fday oday, fmonth omonth, fyear oyear, fdep odep
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fday, fmonth, fyear, fdep;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_DAY_DEP_872637641_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_DAY_DEP;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_DAY_DEP
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J183'>
          <Column id='C143' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C131' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O149' name='TOTAL_PRICE'>
        <Expr id='X149'>
          <Function id='S149' op='sum' aggregateFunc='3'>
            <Column id='C130' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O150' name='SUMDATA'>
        <Expr id='X150'>
          <Function id='S150' op='sum' aggregateFunc='3'>
            <Column id='C144' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O152' name='SYS_COUNTSTAR1'>
        <Expr id='X152'>
          <Function id='S152' op='count' aggregateFunc='1'>
            <NumericVal id='S151' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O153' name='SYS_COUNT2'>
        <Expr id='X153'>
          <Function id='S153' op='count_nonull' aggregateFunc='2'>
            <Column ref='C130'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O154' name='SYS_COUNT3'>
        <Expr id='X154'>
          <Function id='S154' op='count_nonull' aggregateFunc='2'>
            <Column ref='C144'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O159' name='ODAY'>
        <Column ref='C123'>
        </Column>
      </Output>
      <Output id='O162' name='OMONTH'>
        <Column ref='C124'>
        </Column>
      </Output>
      <Output id='O165' name='OYEAR'>
        <Column ref='C125'>
        </Column>
      </Output>
      <Output id='O171' name='ODEP'>
        <Column ref='C127'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C123' tableId='T2' colIndex='1'>
          CAT.SUMBY.T002_FACT.FDAY
        </Column>
        <Column id='C124' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
        <Column id='C125' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C127' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_month_dep
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fmonth omonth, fyear oyear, fdep odep
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fmonth, fyear, fdep;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_MONTH_DEP_935126851_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_MONTH_DEP;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_MONTH_DEP
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J180'>
          <Column id='C140' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C128' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O146' name='TOTAL_PRICE'>
        <Expr id='X146'>
          <Function id='S146' op='sum' aggregateFunc='3'>
            <Column id='C127' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O147' name='SUMDATA'>
        <Expr id='X147'>
          <Function id='S147' op='sum' aggregateFunc='3'>
            <Column id='C141' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O149' name='SYS_COUNTSTAR1'>
        <Expr id='X149'>
          <Function id='S149' op='count' aggregateFunc='1'>
            <NumericVal id='S148' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O150' name='SYS_COUNT2'>
        <Expr id='X150'>
          <Function id='S150' op='count_nonull' aggregateFunc='2'>
            <Column ref='C127'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O151' name='SYS_COUNT3'>
        <Expr id='X151'>
          <Function id='S151' op='count_nonull' aggregateFunc='2'>
            <Column ref='C141'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O159' name='OMONTH'>
        <Column ref='C121'>
        </Column>
      </Output>
      <Output id='O162' name='OYEAR'>
        <Column ref='C122'>
        </Column>
      </Output>
      <Output id='O168' name='ODEP'>
        <Column ref='C124'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C121' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
        <Column id='C122' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C124' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_year_dep
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fyear oyear, fdep odep
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fyear, fdep;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_YEAR_DEP_817426171_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_YEAR_DEP;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_YEAR_DEP
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J177'>
          <Column id='C137' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C125' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O143' name='TOTAL_PRICE'>
        <Expr id='X143'>
          <Function id='S143' op='sum' aggregateFunc='3'>
            <Column id='C124' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O144' name='SUMDATA'>
        <Expr id='X144'>
          <Function id='S144' op='sum' aggregateFunc='3'>
            <Column id='C138' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O146' name='SYS_COUNTSTAR1'>
        <Expr id='X146'>
          <Function id='S146' op='count' aggregateFunc='1'>
            <NumericVal id='S145' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O147' name='SYS_COUNT2'>
        <Expr id='X147'>
          <Function id='S147' op='count_nonull' aggregateFunc='2'>
            <Column ref='C124'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O148' name='SYS_COUNT3'>
        <Expr id='X148'>
          <Function id='S148' op='count_nonull' aggregateFunc='2'>
            <Column ref='C138'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O159' name='OYEAR'>
        <Column ref='C119'>
        </Column>
      </Output>
      <Output id='O165' name='ODEP'>
        <Column ref='C121'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C119' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C121' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_day_state
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fday oday, fmonth omonth, fyear oyear, fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fday, fmonth, fyear, fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_DAY_STATE_385855281_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_DAY_STATE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_DAY_STATE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J183'>
          <Column id='C143' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C131' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O149' name='TOTAL_PRICE'>
        <Expr id='X149'>
          <Function id='S149' op='sum' aggregateFunc='3'>
            <Column id='C130' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O150' name='SUMDATA'>
        <Expr id='X150'>
          <Function id='S150' op='sum' aggregateFunc='3'>
            <Column id='C144' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O152' name='SYS_COUNTSTAR1'>
        <Expr id='X152'>
          <Function id='S152' op='count' aggregateFunc='1'>
            <NumericVal id='S151' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O153' name='SYS_COUNT2'>
        <Expr id='X153'>
          <Function id='S153' op='count_nonull' aggregateFunc='2'>
            <Column ref='C130'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O154' name='SYS_COUNT3'>
        <Expr id='X154'>
          <Function id='S154' op='count_nonull' aggregateFunc='2'>
            <Column ref='C144'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O159' name='ODAY'>
        <Column ref='C123'>
        </Column>
      </Output>
      <Output id='O162' name='OMONTH'>
        <Column ref='C124'>
        </Column>
      </Output>
      <Output id='O165' name='OYEAR'>
        <Column ref='C125'>
        </Column>
      </Output>
      <Output id='O177' name='OSTATE'>
        <Column ref='C129'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C123' tableId='T2' colIndex='1'>
          CAT.SUMBY.T002_FACT.FDAY
        </Column>
        <Column id='C124' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
        <Column id='C125' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C129' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_month_state
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fmonth omonth, fyear oyear, fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fmonth, fyear, fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_MONTH_STATE_615539491_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_MONTH_STATE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_MONTH_STATE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J180'>
          <Column id='C140' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C128' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O146' name='TOTAL_PRICE'>
        <Expr id='X146'>
          <Function id='S146' op='sum' aggregateFunc='3'>
            <Column id='C127' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O147' name='SUMDATA'>
        <Expr id='X147'>
          <Function id='S147' op='sum' aggregateFunc='3'>
            <Column id='C141' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O149' name='SYS_COUNTSTAR1'>
        <Expr id='X149'>
          <Function id='S149' op='count' aggregateFunc='1'>
            <NumericVal id='S148' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O150' name='SYS_COUNT2'>
        <Expr id='X150'>
          <Function id='S150' op='count_nonull' aggregateFunc='2'>
            <Column ref='C127'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O151' name='SYS_COUNT3'>
        <Expr id='X151'>
          <Function id='S151' op='count_nonull' aggregateFunc='2'>
            <Column ref='C141'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O159' name='OMONTH'>
        <Column ref='C121'>
        </Column>
      </Output>
      <Output id='O162' name='OYEAR'>
        <Column ref='C122'>
        </Column>
      </Output>
      <Output id='O174' name='OSTATE'>
        <Column ref='C126'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C121' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
        <Column id='C122' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C126' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_year_state
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fyear oyear, fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fyear, fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_YEAR_STATE_341666712_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_YEAR_STATE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_YEAR_STATE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J177'>
          <Column id='C137' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C125' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O143' name='TOTAL_PRICE'>
        <Expr id='X143'>
          <Function id='S143' op='sum' aggregateFunc='3'>
            <Column id='C124' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O144' name='SUMDATA'>
        <Expr id='X144'>
          <Function id='S144' op='sum' aggregateFunc='3'>
            <Column id='C138' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O146' name='SYS_COUNTSTAR1'>
        <Expr id='X146'>
          <Function id='S146' op='count' aggregateFunc='1'>
            <NumericVal id='S145' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O147' name='SYS_COUNT2'>
        <Expr id='X147'>
          <Function id='S147' op='count_nonull' aggregateFunc='2'>
            <Column ref='C124'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O148' name='SYS_COUNT3'>
        <Expr id='X148'>
          <Function id='S148' op='count_nonull' aggregateFunc='2'>
            <Column ref='C138'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O159' name='OYEAR'>
        <Column ref='C119'>
        </Column>
      </Output>
      <Output id='O171' name='OSTATE'>
        <Column ref='C123'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C119' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C123' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>--===========================================
>>-- Three dimentions
>>--===========================================
>>
>>create mv sumby_day_item_store
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fday oday, fmonth omonth, fyear oyear,
+>	           fitem oitem, fdep odep, 
+>	           fstore ostore, fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fday, fmonth, fyear, 
+>	             fitem, fdep, 
+>	             fstore, fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_DAY_ITEM_STORE_141984332_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_DAY_ITEM_STORE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_DAY_ITEM_STORE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J192'>
          <Column id='C152' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C140' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O158' name='TOTAL_PRICE'>
        <Expr id='X158'>
          <Function id='S158' op='sum' aggregateFunc='3'>
            <Column id='C139' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O159' name='SUMDATA'>
        <Expr id='X159'>
          <Function id='S159' op='sum' aggregateFunc='3'>
            <Column id='C153' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O161' name='SYS_COUNTSTAR1'>
        <Expr id='X161'>
          <Function id='S161' op='count' aggregateFunc='1'>
            <NumericVal id='S160' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O162' name='SYS_COUNT2'>
        <Expr id='X162'>
          <Function id='S162' op='count_nonull' aggregateFunc='2'>
            <Column ref='C139'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O163' name='SYS_COUNT3'>
        <Expr id='X163'>
          <Function id='S163' op='count_nonull' aggregateFunc='2'>
            <Column ref='C153'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O168' name='ODAY'>
        <Column ref='C132'>
        </Column>
      </Output>
      <Output id='O171' name='OMONTH'>
        <Column ref='C133'>
        </Column>
      </Output>
      <Output id='O174' name='OYEAR'>
        <Column ref='C134'>
        </Column>
      </Output>
      <Output id='O177' name='OITEM'>
        <Column ref='C135'>
        </Column>
      </Output>
      <Output id='O180' name='ODEP'>
        <Column ref='C136'>
        </Column>
      </Output>
      <Output id='O183' name='OSTORE'>
        <Column ref='C137'>
        </Column>
      </Output>
      <Output id='O186' name='OSTATE'>
        <Column ref='C138'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C132' tableId='T2' colIndex='1'>
          CAT.SUMBY.T002_FACT.FDAY
        </Column>
        <Column id='C133' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
        <Column id='C134' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C135' tableId='T2' colIndex='4'>
          CAT.SUMBY.T002_FACT.FITEM
        </Column>
        <Column id='C136' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
        <Column id='C137' tableId='T2' colIndex='6'>
          CAT.SUMBY.T002_FACT.FSTORE
        </Column>
        <Column id='C138' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_month_item_store
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fmonth omonth, fyear oyear,
+>	           fitem oitem, fdep odep, 
+>	           fstore ostore, fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fmonth, fyear,
+>	             fitem, fdep, 
+>	             fstore, fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_MONTH_ITEM_STO_572237542_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_MONTH_ITEM_STORE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_MONTH_ITEM_STORE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J189'>
          <Column id='C149' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C137' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O155' name='TOTAL_PRICE'>
        <Expr id='X155'>
          <Function id='S155' op='sum' aggregateFunc='3'>
            <Column id='C136' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O156' name='SUMDATA'>
        <Expr id='X156'>
          <Function id='S156' op='sum' aggregateFunc='3'>
            <Column id='C150' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O158' name='SYS_COUNTSTAR1'>
        <Expr id='X158'>
          <Function id='S158' op='count' aggregateFunc='1'>
            <NumericVal id='S157' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O159' name='SYS_COUNT2'>
        <Expr id='X159'>
          <Function id='S159' op='count_nonull' aggregateFunc='2'>
            <Column ref='C136'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O160' name='SYS_COUNT3'>
        <Expr id='X160'>
          <Function id='S160' op='count_nonull' aggregateFunc='2'>
            <Column ref='C150'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O168' name='OMONTH'>
        <Column ref='C130'>
        </Column>
      </Output>
      <Output id='O171' name='OYEAR'>
        <Column ref='C131'>
        </Column>
      </Output>
      <Output id='O174' name='OITEM'>
        <Column ref='C132'>
        </Column>
      </Output>
      <Output id='O177' name='ODEP'>
        <Column ref='C133'>
        </Column>
      </Output>
      <Output id='O180' name='OSTORE'>
        <Column ref='C134'>
        </Column>
      </Output>
      <Output id='O183' name='OSTATE'>
        <Column ref='C135'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C130' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
        <Column id='C131' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C132' tableId='T2' colIndex='4'>
          CAT.SUMBY.T002_FACT.FITEM
        </Column>
        <Column id='C133' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
        <Column id='C134' tableId='T2' colIndex='6'>
          CAT.SUMBY.T002_FACT.FSTORE
        </Column>
        <Column id='C135' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_year_item_store
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fyear oyear,
+>	           fitem oitem, fdep odep, 
+>	           fstore ostore, fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fyear, 
+>	             fitem, fdep, 
+>	             fstore, fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_YEAR_ITEM_STOR_177657752_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_YEAR_ITEM_STORE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_YEAR_ITEM_STORE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J186'>
          <Column id='C146' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C134' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O152' name='TOTAL_PRICE'>
        <Expr id='X152'>
          <Function id='S152' op='sum' aggregateFunc='3'>
            <Column id='C133' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O153' name='SUMDATA'>
        <Expr id='X153'>
          <Function id='S153' op='sum' aggregateFunc='3'>
            <Column id='C147' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O155' name='SYS_COUNTSTAR1'>
        <Expr id='X155'>
          <Function id='S155' op='count' aggregateFunc='1'>
            <NumericVal id='S154' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O156' name='SYS_COUNT2'>
        <Expr id='X156'>
          <Function id='S156' op='count_nonull' aggregateFunc='2'>
            <Column ref='C133'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O157' name='SYS_COUNT3'>
        <Expr id='X157'>
          <Function id='S157' op='count_nonull' aggregateFunc='2'>
            <Column ref='C147'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O168' name='OYEAR'>
        <Column ref='C128'>
        </Column>
      </Output>
      <Output id='O171' name='OITEM'>
        <Column ref='C129'>
        </Column>
      </Output>
      <Output id='O174' name='ODEP'>
        <Column ref='C130'>
        </Column>
      </Output>
      <Output id='O177' name='OSTORE'>
        <Column ref='C131'>
        </Column>
      </Output>
      <Output id='O180' name='OSTATE'>
        <Column ref='C132'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C128' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C129' tableId='T2' colIndex='4'>
          CAT.SUMBY.T002_FACT.FITEM
        </Column>
        <Column id='C130' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
        <Column id='C131' tableId='T2' colIndex='6'>
          CAT.SUMBY.T002_FACT.FSTORE
        </Column>
        <Column id='C132' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>--===========================================
>>
>>create mv sumby_day_item_state
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fday oday, fmonth omonth, fyear oyear,
+>	           fitem oitem, fdep odep, 
+>	           fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fday, fmonth, fyear, 
+>	             fitem, fdep, 
+>	             fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_DAY_ITEM_STATE_231632172_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_DAY_ITEM_STATE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_DAY_ITEM_STATE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J189'>
          <Column id='C149' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C137' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O155' name='TOTAL_PRICE'>
        <Expr id='X155'>
          <Function id='S155' op='sum' aggregateFunc='3'>
            <Column id='C136' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O156' name='SUMDATA'>
        <Expr id='X156'>
          <Function id='S156' op='sum' aggregateFunc='3'>
            <Column id='C150' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O158' name='SYS_COUNTSTAR1'>
        <Expr id='X158'>
          <Function id='S158' op='count' aggregateFunc='1'>
            <NumericVal id='S157' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O159' name='SYS_COUNT2'>
        <Expr id='X159'>
          <Function id='S159' op='count_nonull' aggregateFunc='2'>
            <Column ref='C136'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O160' name='SYS_COUNT3'>
        <Expr id='X160'>
          <Function id='S160' op='count_nonull' aggregateFunc='2'>
            <Column ref='C150'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O165' name='ODAY'>
        <Column ref='C129'>
        </Column>
      </Output>
      <Output id='O168' name='OMONTH'>
        <Column ref='C130'>
        </Column>
      </Output>
      <Output id='O171' name='OYEAR'>
        <Column ref='C131'>
        </Column>
      </Output>
      <Output id='O174' name='OITEM'>
        <Column ref='C132'>
        </Column>
      </Output>
      <Output id='O177' name='ODEP'>
        <Column ref='C133'>
        </Column>
      </Output>
      <Output id='O183' name='OSTATE'>
        <Column ref='C135'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C129' tableId='T2' colIndex='1'>
          CAT.SUMBY.T002_FACT.FDAY
        </Column>
        <Column id='C130' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
        <Column id='C131' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C132' tableId='T2' colIndex='4'>
          CAT.SUMBY.T002_FACT.FITEM
        </Column>
        <Column id='C133' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
        <Column id='C135' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_month_item_state
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fmonth omonth, fyear oyear,
+>	           fitem oitem, fdep odep, 
+>	           fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fmonth, fyear, 
+>	             fitem, fdep, 
+>	             fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_MONTH_ITEM_STA_736521482_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_MONTH_ITEM_STATE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_MONTH_ITEM_STATE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J186'>
          <Column id='C146' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C134' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O152' name='TOTAL_PRICE'>
        <Expr id='X152'>
          <Function id='S152' op='sum' aggregateFunc='3'>
            <Column id='C133' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O153' name='SUMDATA'>
        <Expr id='X153'>
          <Function id='S153' op='sum' aggregateFunc='3'>
            <Column id='C147' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O155' name='SYS_COUNTSTAR1'>
        <Expr id='X155'>
          <Function id='S155' op='count' aggregateFunc='1'>
            <NumericVal id='S154' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O156' name='SYS_COUNT2'>
        <Expr id='X156'>
          <Function id='S156' op='count_nonull' aggregateFunc='2'>
            <Column ref='C133'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O157' name='SYS_COUNT3'>
        <Expr id='X157'>
          <Function id='S157' op='count_nonull' aggregateFunc='2'>
            <Column ref='C147'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O165' name='OMONTH'>
        <Column ref='C127'>
        </Column>
      </Output>
      <Output id='O168' name='OYEAR'>
        <Column ref='C128'>
        </Column>
      </Output>
      <Output id='O171' name='OITEM'>
        <Column ref='C129'>
        </Column>
      </Output>
      <Output id='O174' name='ODEP'>
        <Column ref='C130'>
        </Column>
      </Output>
      <Output id='O180' name='OSTATE'>
        <Column ref='C132'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C127' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
        <Column id='C128' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C129' tableId='T2' colIndex='4'>
          CAT.SUMBY.T002_FACT.FITEM
        </Column>
        <Column id='C130' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
        <Column id='C132' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_year_item_state
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fyear oyear,
+>	           fitem oitem, fdep odep, 
+>	           fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fyear, 
+>	             fitem, fdep, 
+>	             fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_YEAR_ITEM_STAT_672339692_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_YEAR_ITEM_STATE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_YEAR_ITEM_STATE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J183'>
          <Column id='C143' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C131' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O149' name='TOTAL_PRICE'>
        <Expr id='X149'>
          <Function id='S149' op='sum' aggregateFunc='3'>
            <Column id='C130' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O150' name='SUMDATA'>
        <Expr id='X150'>
          <Function id='S150' op='sum' aggregateFunc='3'>
            <Column id='C144' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O152' name='SYS_COUNTSTAR1'>
        <Expr id='X152'>
          <Function id='S152' op='count' aggregateFunc='1'>
            <NumericVal id='S151' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O153' name='SYS_COUNT2'>
        <Expr id='X153'>
          <Function id='S153' op='count_nonull' aggregateFunc='2'>
            <Column ref='C130'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O154' name='SYS_COUNT3'>
        <Expr id='X154'>
          <Function id='S154' op='count_nonull' aggregateFunc='2'>
            <Column ref='C144'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O165' name='OYEAR'>
        <Column ref='C125'>
        </Column>
      </Output>
      <Output id='O168' name='OITEM'>
        <Column ref='C126'>
        </Column>
      </Output>
      <Output id='O171' name='ODEP'>
        <Column ref='C127'>
        </Column>
      </Output>
      <Output id='O177' name='OSTATE'>
        <Column ref='C129'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C125' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C126' tableId='T2' colIndex='4'>
          CAT.SUMBY.T002_FACT.FITEM
        </Column>
        <Column id='C127' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
        <Column id='C129' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>--===========================================
>>
>>create mv sumby_day_dep_store
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fday oday, fmonth omonth, fyear oyear,
+>	           fdep odep, 
+>	           fstore ostore, fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fday, fmonth, fyear, 
+>	             fdep, 
+>	             fstore, fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_DAY_DEP_STORE_668457323_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_DAY_DEP_STORE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_DAY_DEP_STORE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J189'>
          <Column id='C149' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C137' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O155' name='TOTAL_PRICE'>
        <Expr id='X155'>
          <Function id='S155' op='sum' aggregateFunc='3'>
            <Column id='C136' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O156' name='SUMDATA'>
        <Expr id='X156'>
          <Function id='S156' op='sum' aggregateFunc='3'>
            <Column id='C150' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O158' name='SYS_COUNTSTAR1'>
        <Expr id='X158'>
          <Function id='S158' op='count' aggregateFunc='1'>
            <NumericVal id='S157' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O159' name='SYS_COUNT2'>
        <Expr id='X159'>
          <Function id='S159' op='count_nonull' aggregateFunc='2'>
            <Column ref='C136'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O160' name='SYS_COUNT3'>
        <Expr id='X160'>
          <Function id='S160' op='count_nonull' aggregateFunc='2'>
            <Column ref='C150'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O165' name='ODAY'>
        <Column ref='C129'>
        </Column>
      </Output>
      <Output id='O168' name='OMONTH'>
        <Column ref='C130'>
        </Column>
      </Output>
      <Output id='O171' name='OYEAR'>
        <Column ref='C131'>
        </Column>
      </Output>
      <Output id='O177' name='ODEP'>
        <Column ref='C133'>
        </Column>
      </Output>
      <Output id='O180' name='OSTORE'>
        <Column ref='C134'>
        </Column>
      </Output>
      <Output id='O183' name='OSTATE'>
        <Column ref='C135'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C129' tableId='T2' colIndex='1'>
          CAT.SUMBY.T002_FACT.FDAY
        </Column>
        <Column id='C130' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
        <Column id='C131' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C133' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
        <Column id='C134' tableId='T2' colIndex='6'>
          CAT.SUMBY.T002_FACT.FSTORE
        </Column>
        <Column id='C135' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_month_dep_store
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fmonth omonth, fyear oyear,
+>	           fdep odep, 
+>	           fstore ostore, fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fmonth, fyear, 
+>	             fdep, 
+>	             fstore, fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_MONTH_DEP_STOR_133298633_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_MONTH_DEP_STORE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_MONTH_DEP_STORE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J186'>
          <Column id='C146' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C134' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O152' name='TOTAL_PRICE'>
        <Expr id='X152'>
          <Function id='S152' op='sum' aggregateFunc='3'>
            <Column id='C133' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O153' name='SUMDATA'>
        <Expr id='X153'>
          <Function id='S153' op='sum' aggregateFunc='3'>
            <Column id='C147' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O155' name='SYS_COUNTSTAR1'>
        <Expr id='X155'>
          <Function id='S155' op='count' aggregateFunc='1'>
            <NumericVal id='S154' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O156' name='SYS_COUNT2'>
        <Expr id='X156'>
          <Function id='S156' op='count_nonull' aggregateFunc='2'>
            <Column ref='C133'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O157' name='SYS_COUNT3'>
        <Expr id='X157'>
          <Function id='S157' op='count_nonull' aggregateFunc='2'>
            <Column ref='C147'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O165' name='OMONTH'>
        <Column ref='C127'>
        </Column>
      </Output>
      <Output id='O168' name='OYEAR'>
        <Column ref='C128'>
        </Column>
      </Output>
      <Output id='O174' name='ODEP'>
        <Column ref='C130'>
        </Column>
      </Output>
      <Output id='O177' name='OSTORE'>
        <Column ref='C131'>
        </Column>
      </Output>
      <Output id='O180' name='OSTATE'>
        <Column ref='C132'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C127' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
        <Column id='C128' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C130' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
        <Column id='C131' tableId='T2' colIndex='6'>
          CAT.SUMBY.T002_FACT.FSTORE
        </Column>
        <Column id='C132' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_year_dep_store
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fyear oyear,
+>	           fdep odep, 
+>	           fstore ostore, fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fyear, 
+>	             fdep, 
+>	             fstore, fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_YEAR_DEP_STORE_869955153_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_YEAR_DEP_STORE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_YEAR_DEP_STORE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J183'>
          <Column id='C143' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C131' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O149' name='TOTAL_PRICE'>
        <Expr id='X149'>
          <Function id='S149' op='sum' aggregateFunc='3'>
            <Column id='C130' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O150' name='SUMDATA'>
        <Expr id='X150'>
          <Function id='S150' op='sum' aggregateFunc='3'>
            <Column id='C144' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O152' name='SYS_COUNTSTAR1'>
        <Expr id='X152'>
          <Function id='S152' op='count' aggregateFunc='1'>
            <NumericVal id='S151' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O153' name='SYS_COUNT2'>
        <Expr id='X153'>
          <Function id='S153' op='count_nonull' aggregateFunc='2'>
            <Column ref='C130'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O154' name='SYS_COUNT3'>
        <Expr id='X154'>
          <Function id='S154' op='count_nonull' aggregateFunc='2'>
            <Column ref='C144'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O165' name='OYEAR'>
        <Column ref='C125'>
        </Column>
      </Output>
      <Output id='O171' name='ODEP'>
        <Column ref='C127'>
        </Column>
      </Output>
      <Output id='O174' name='OSTORE'>
        <Column ref='C128'>
        </Column>
      </Output>
      <Output id='O177' name='OSTATE'>
        <Column ref='C129'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C125' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C127' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
        <Column id='C128' tableId='T2' colIndex='6'>
          CAT.SUMBY.T002_FACT.FSTORE
        </Column>
        <Column id='C129' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>--===========================================
>>
>>create mv sumby_day_dep_state
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fday oday, fmonth omonth, fyear oyear,
+>	           fdep odep, 
+>	           fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fday, fmonth, fyear, 
+>	             fdep, 
+>	             fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_DAY_DEP_STATE_336519563_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_DAY_DEP_STATE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_DAY_DEP_STATE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J186'>
          <Column id='C146' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C134' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O152' name='TOTAL_PRICE'>
        <Expr id='X152'>
          <Function id='S152' op='sum' aggregateFunc='3'>
            <Column id='C133' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O153' name='SUMDATA'>
        <Expr id='X153'>
          <Function id='S153' op='sum' aggregateFunc='3'>
            <Column id='C147' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O155' name='SYS_COUNTSTAR1'>
        <Expr id='X155'>
          <Function id='S155' op='count' aggregateFunc='1'>
            <NumericVal id='S154' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O156' name='SYS_COUNT2'>
        <Expr id='X156'>
          <Function id='S156' op='count_nonull' aggregateFunc='2'>
            <Column ref='C133'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O157' name='SYS_COUNT3'>
        <Expr id='X157'>
          <Function id='S157' op='count_nonull' aggregateFunc='2'>
            <Column ref='C147'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O162' name='ODAY'>
        <Column ref='C126'>
        </Column>
      </Output>
      <Output id='O165' name='OMONTH'>
        <Column ref='C127'>
        </Column>
      </Output>
      <Output id='O168' name='OYEAR'>
        <Column ref='C128'>
        </Column>
      </Output>
      <Output id='O174' name='ODEP'>
        <Column ref='C130'>
        </Column>
      </Output>
      <Output id='O180' name='OSTATE'>
        <Column ref='C132'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C126' tableId='T2' colIndex='1'>
          CAT.SUMBY.T002_FACT.FDAY
        </Column>
        <Column id='C127' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
        <Column id='C128' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C130' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
        <Column id='C132' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_month_dep_state
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fmonth omonth, fyear oyear,
+>	           fdep odep, 
+>	           fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fmonth, fyear, 
+>	             fdep, 
+>	             fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_MONTH_DEP_STAT_457233183_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_MONTH_DEP_STATE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_MONTH_DEP_STATE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J183'>
          <Column id='C143' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C131' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O149' name='TOTAL_PRICE'>
        <Expr id='X149'>
          <Function id='S149' op='sum' aggregateFunc='3'>
            <Column id='C130' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O150' name='SUMDATA'>
        <Expr id='X150'>
          <Function id='S150' op='sum' aggregateFunc='3'>
            <Column id='C144' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O152' name='SYS_COUNTSTAR1'>
        <Expr id='X152'>
          <Function id='S152' op='count' aggregateFunc='1'>
            <NumericVal id='S151' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O153' name='SYS_COUNT2'>
        <Expr id='X153'>
          <Function id='S153' op='count_nonull' aggregateFunc='2'>
            <Column ref='C130'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O154' name='SYS_COUNT3'>
        <Expr id='X154'>
          <Function id='S154' op='count_nonull' aggregateFunc='2'>
            <Column ref='C144'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O162' name='OMONTH'>
        <Column ref='C124'>
        </Column>
      </Output>
      <Output id='O165' name='OYEAR'>
        <Column ref='C125'>
        </Column>
      </Output>
      <Output id='O171' name='ODEP'>
        <Column ref='C127'>
        </Column>
      </Output>
      <Output id='O177' name='OSTATE'>
        <Column ref='C129'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C124' tableId='T2' colIndex='2'>
          CAT.SUMBY.T002_FACT.FMONTH
        </Column>
        <Column id='C125' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C127' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
        <Column id='C129' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>create mv sumby_year_dep_state
+>	refresh on request
+>	initialized on create
+>	as  select sum(fprice) total_price, 
+>		   sum(dimdata) sumdata,
+>		   fyear oyear,
+>	           fdep odep, 
+>	           fstate ostate
+>	    from T002_FACT, T002_DIM
+>	    where dimkey=dkey
+>	    group by fyear, 
+>	             fdep, 
+>	             fstate;

*** WARNING[12112] A secondary index CAT.SUMBY.SUMBY_YEAR_DEP_STATE_267448693_9992 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>set pattern $$MVName$$ SUMBY_YEAR_DEP_STATE;
>>obey TEST002(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.SUMBY.SUMBY_YEAR_DEP_STATE
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223873639188679' numCols='3'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table id='T2' TS='212223873639188679' numCols='10'>
          CAT.SUMBY.T002_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J180'>
          <Column id='C140' tableId='T1' colIndex='1'>
            CAT.SUMBY.T002_DIM.DKEY
          </Column>
          <Column id='C128' tableId='T2' colIndex='9'>
            CAT.SUMBY.T002_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O146' name='TOTAL_PRICE'>
        <Expr id='X146'>
          <Function id='S146' op='sum' aggregateFunc='3'>
            <Column id='C127' tableId='T2' colIndex='8'>
              CAT.SUMBY.T002_FACT.FPRICE
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O147' name='SUMDATA'>
        <Expr id='X147'>
          <Function id='S147' op='sum' aggregateFunc='3'>
            <Column id='C141' tableId='T1' colIndex='2'>
              CAT.SUMBY.T002_DIM.DIMDATA
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O149' name='SYS_COUNTSTAR1'>
        <Expr id='X149'>
          <Function id='S149' op='count' aggregateFunc='1'>
            <NumericVal id='S148' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O150' name='SYS_COUNT2'>
        <Expr id='X150'>
          <Function id='S150' op='count_nonull' aggregateFunc='2'>
            <Column ref='C127'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O151' name='SYS_COUNT3'>
        <Expr id='X151'>
          <Function id='S151' op='count_nonull' aggregateFunc='2'>
            <Column ref='C141'>
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O162' name='OYEAR'>
        <Column ref='C122'>
        </Column>
      </Output>
      <Output id='O168' name='ODEP'>
        <Column ref='C124'>
        </Column>
      </Output>
      <Output id='O174' name='OSTATE'>
        <Column ref='C126'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C122' tableId='T2' colIndex='3'>
          CAT.SUMBY.T002_FACT.FYEAR
        </Column>
        <Column id='C124' tableId='T2' colIndex='5'>
          CAT.SUMBY.T002_FACT.FDEP
        </Column>
        <Column id='C126' tableId='T2' colIndex='7'>
          CAT.SUMBY.T002_FACT.FSTATE
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>--===========================================
>>--===========================================
>>obey TEST002(match1);
>>--===========================================
>>-- Query 1, matching SUMBY_ITEM
>>--===========================================
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST002(query1);
>>prepare QueryStmt from
+>  select sum(fprice) total_price, 
+>         sum(dimdata) sumdata,
+>         fitem oitem, fdep odep
+>  from T002_FACT, T002_DIM
+>  where dimkey=dkey
+>  group by fitem, fdep
+>  order by fitem, fdep;

--- SQL command prepared.
>>
>>
>>set pattern $$QueryName$$ SUMBY_QUERY_ITEM;
>>obey TEST002(dump_Query);
>>--===========================================
>>--== Create the query descriptor XML
>>--===========================================
>>
>>log $$QueryName$$.tmp clear;
>>sh sh ./CropDescriptor.ksh -q -i $$QueryName$$.TMP -o $$QueryName$$.xml;
>>-- and once for the test log file
>>execute QueryStmt;

Query Descriptor                                                      
----------------------------------------------------------------------

<Query>                                                               
  <Version>                                                           
    1.0                                                               
  </Version>                                                          
  <Misc rewriteLevel='3'>                                             
  </Misc>                                                             
  <JBB id='B0'>                                                       
    <Hub>                                                             
      <JBBCList>                                                      
        <Table id='T1' TS='212223873639188679' numCols='3'>           
          CAT.SUMBY.T002_DIM                                          
          <Key>                                                       
            <Column id='C21' tableId='T1' colIndex='0' isNullable='0'>
              CAT.SUMBY.T002_DIM.SYSKEY                               
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
        <Table id='T2' TS='212223873639188679' numCols='10'>          
          CAT.SUMBY.T002_FACT                                         
          <Key>                                                       
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'> 
              CAT.SUMBY.T002_FACT.SYSKEY                              
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
      </JBBCList>                                                     
      <JoinPredList>                                                  
        <JoinPred id='J58'>                                           
          <Column id='C22' tableId='T1' colIndex='1'>                 
            CAT.SUMBY.T002_DIM.DKEY                                   
          </Column>                                                   
          <Column id='C10' tableId='T2' colIndex='9'>                 
            CAT.SUMBY.T002_FACT.DIMKEY                                
          </Column>                                                   
        </JoinPred>                                                   
      </JoinPredList>                                                 
    </Hub>                                                            
    <ExtraHub>                                                        
    </ExtraHub>                                                       
    <OutputList>                                                      
      <Output id='O28'>                                               
        <Expr id='X28'>                                               
          <Function id='S28' op='sum' aggregateFunc='3'>              
            <Column id='C9' tableId='T2' colIndex='8'>                
              CAT.SUMBY.T002_FACT.FPRICE                              
            </Column>                                                 
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O29'>                                               
        <Expr id='X29'>                                               
          <Function id='S29' op='sum' aggregateFunc='3'>              
            <Column id='C23' tableId='T1' colIndex='2'>               
              CAT.SUMBY.T002_DIM.DIMDATA                              
            </Column>                                                 
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O43'>                                               
        <Column ref='C5'>                                             
        </Column>                                                     
      </Output>                                                       
      <Output id='O46'>                                               
        <Column ref='C6'>                                             
        </Column>                                                     
      </Output>                                                       
    </OutputList>                                                     
    <GroupBy id='G3'>                                                 
      <Primary>                                                       
        <Column id='C5' tableId='T2' colIndex='4'>                    
          CAT.SUMBY.T002_FACT.FITEM                                   
        </Column>                                                     
        <Column id='C6' tableId='T2' colIndex='5'>                    
          CAT.SUMBY.T002_FACT.FDEP                                    
        </Column>                                                     
      </Primary>                                                      
    </GroupBy>                                                        
  </JBB>                                                              
</Query>                                                              

--- 79 row(s) selected.
>>
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

--- SQL operation complete.
>>
>>sh echo INITIALIZE                                 >> sumby1.in;
>>sh echo PUBLISH SUMBY_NONE.xml                     >> sumby1.in;
>>sh echo PUBLISH SUMBY_DAY.xml                      >> sumby1.in;
>>sh echo PUBLISH SUMBY_DAY_DEP.xml                  >> sumby1.in;
>>sh echo PUBLISH SUMBY_DAY_DEP_STATE.xml            >> sumby1.in;
>>sh echo PUBLISH SUMBY_DAY_DEP_STORE.xml            >> sumby1.in;
>>sh echo PUBLISH SUMBY_DAY_ITEM.xml                 >> sumby1.in;
>>sh echo PUBLISH SUMBY_DAY_ITEM_STATE.xml           >> sumby1.in;
>>sh echo PUBLISH SUMBY_DAY_ITEM_STORE.xml           >> sumby1.in;
>>sh echo PUBLISH SUMBY_DAY_STATE.xml                >> sumby1.in;
>>sh echo PUBLISH SUMBY_DAY_STORE.xml                >> sumby1.in;
>>sh echo PUBLISH SUMBY_DEP.xml                      >> sumby1.in;
>>sh echo PUBLISH SUMBY_ITEM.xml                     >> sumby1.in;
>>sh echo PUBLISH SUMBY_MONTH.xml                    >> sumby1.in;
>>sh echo PUBLISH SUMBY_MONTH_DEP.xml                >> sumby1.in;
>>sh echo PUBLISH SUMBY_MONTH_DEP_STATE.xml          >> sumby1.in;
>>sh echo PUBLISH SUMBY_MONTH_DEP_STORE.xml          >> sumby1.in;
>>sh echo PUBLISH SUMBY_MONTH_ITEM.xml               >> sumby1.in;
>>sh echo PUBLISH SUMBY_MONTH_ITEM_STATE.xml         >> sumby1.in;
>>sh echo PUBLISH SUMBY_MONTH_ITEM_STORE.xml         >> sumby1.in;
>>sh echo PUBLISH SUMBY_MONTH_STATE.xml              >> sumby1.in;
>>sh echo PUBLISH SUMBY_MONTH_STORE.xml              >> sumby1.in;
>>sh echo PUBLISH SUMBY_STATE.xml                    >> sumby1.in;
>>sh echo PUBLISH SUMBY_STORE.xml                    >> sumby1.in;
>>sh echo PUBLISH SUMBY_YEAR.xml                     >> sumby1.in;
>>sh echo PUBLISH SUMBY_YEAR_DEP.xml                 >> sumby1.in;
>>sh echo PUBLISH SUMBY_YEAR_DEP_STATE.xml           >> sumby1.in;
>>sh echo PUBLISH SUMBY_YEAR_DEP_STORE.xml           >> sumby1.in;
>>sh echo PUBLISH SUMBY_YEAR_ITEM.xml                >> sumby1.in;
>>sh echo PUBLISH SUMBY_YEAR_ITEM_STATE.xml          >> sumby1.in;
>>sh echo PUBLISH SUMBY_YEAR_ITEM_STORE.xml          >> sumby1.in;
>>sh echo PUBLISH SUMBY_YEAR_STATE.xml               >> sumby1.in;
>>sh echo PUBLISH SUMBY_YEAR_STORE.xml               >> sumby1.in;
>>sh echo MATCH SUMBY_QUERY_ITEM.xml                 >> sumby1.in;
>>
>>-- Run QMS as a command line tool
>>sh sh -c "$QMS sumby1.in sumby.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table ref='T2'>
          CAT.SUMBY.T002_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_NONE
          </MVName>
          <OutputList>
            <Output ref='C9' result='Provided'>
              <MVColumn ref='C9'>
                FPRICE
              </MVColumn>
            </Output>
            <Output ref='C23' result='Provided'>
              <MVColumn ref='C23'>
                DIMDATA
              </MVColumn>
            </Output>
            <Output ref='O43' result='Provided'>
              <MVColumn ref='C5'>
                FITEM
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                FDEP
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table ref='T2'>
          CAT.SUMBY.T002_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_DAY_ITEM
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O43' result='Provided'>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S29'>
                    SUMDATA
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O28' result='NotProvided'>
              <Expr ref='X28'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S28'>
                    TOTAL_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_DAY_ITEM_STATE
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O43' result='Provided'>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S29'>
                    SUMDATA
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O28' result='NotProvided'>
              <Expr ref='X28'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S28'>
                    TOTAL_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_DAY_ITEM_STORE
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O43' result='Provided'>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S29'>
                    SUMDATA
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O28' result='NotProvided'>
              <Expr ref='X28'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S28'>
                    TOTAL_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_ITEM
          </MVName>
          <GroupBy ref='G3' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O28' result='Provided'>
              <MVColumn ref='X28'>
                TOTAL_PRICE
              </MVColumn>
            </Output>
            <Output ref='O29' result='Provided'>
              <MVColumn ref='X29'>
                SUMDATA
              </MVColumn>
            </Output>
            <Output ref='O43' result='Provided'>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_MONTH_ITEM
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O43' result='Provided'>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S29'>
                    SUMDATA
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O28' result='NotProvided'>
              <Expr ref='X28'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S28'>
                    TOTAL_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_MONTH_ITEM_STATE
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O43' result='Provided'>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S29'>
                    SUMDATA
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O28' result='NotProvided'>
              <Expr ref='X28'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S28'>
                    TOTAL_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_MONTH_ITEM_STORE
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O43' result='Provided'>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S29'>
                    SUMDATA
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O28' result='NotProvided'>
              <Expr ref='X28'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S28'>
                    TOTAL_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_YEAR_ITEM
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O43' result='Provided'>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S29'>
                    SUMDATA
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O28' result='NotProvided'>
              <Expr ref='X28'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S28'>
                    TOTAL_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_YEAR_ITEM_STATE
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O43' result='Provided'>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S29'>
                    SUMDATA
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O28' result='NotProvided'>
              <Expr ref='X28'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S28'>
                    TOTAL_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_YEAR_ITEM_STORE
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O43' result='Provided'>
              <MVColumn ref='C5'>
                OITEM
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S29'>
                    SUMDATA
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O28' result='NotProvided'>
              <Expr ref='X28'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S28'>
                    TOTAL_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
  </JbbResult>
</Result>
>>
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.SUMBY.SUMBY_ITEM';

--- SQL operation complete.
>>-- Verify the MV is picked by the optimizer.
>>set pattern $$QueryName$$ query1;
>>obey TEST002(compare);
>>
>>obey TEST002($$QueryName$$);
>>prepare QueryStmt from
+>  select sum(fprice) total_price, 
+>         sum(dimdata) sumdata,
+>         fitem oitem, fdep odep
+>  from T002_FACT, T002_DIM
+>  where dimkey=dkey
+>  group by fitem, fdep
+>  order by fitem, fdep;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.SUMBY.SUMBY_ITEM                                        

--- 1 row(s) selected.
>>
>>execute QueryStmt;

TOTAL_PRICE            SUMDATA               OITEM        ODEP       
---------------------  --------------------  -----------  -----------

                39.00                     6            1            1
                32.00                     8            1            2
                66.00                    12            1            3
               174.00                    12            1            4
                76.00                     4            1            5
               177.00                    19            2            1
               167.00                     7            2            2
                62.00                     8            2            3
                79.00                    10            2            4
               138.00                    12            2            5
                79.00                    13            3            1
               182.00                    18            3            2
               145.00                    13            3            3
               124.00                     8            3            4
               154.00                    14            4            1
                25.00                     4            4            2
               184.00                    21            4            3
               148.00                    14            4            4
                76.00                     8            4            5
                15.00                    14            5            1
                96.00                     9            5            2
                90.00                     3            5            3
                75.00                     7            5            4
               117.00                    11            5            5

--- 24 row(s) selected.
>>
>>log;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST002($$QueryName$$);
>>prepare QueryStmt from
+>  select sum(fprice) total_price, 
+>         sum(dimdata) sumdata,
+>         fitem oitem, fdep odep
+>  from T002_FACT, T002_DIM
+>  where dimkey=dkey
+>  group by fitem, fdep
+>  order by fitem, fdep;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.SUMBY.T002_DIM                                          
FILE_SCAN                       CAT.SUMBY.T002_FACT                                         

--- 2 row(s) selected.
>>
>>execute QueryStmt;

TOTAL_PRICE            SUMDATA               OITEM        ODEP       
---------------------  --------------------  -----------  -----------

                39.00                     6            1            1
                32.00                     8            1            2
                66.00                    12            1            3
               174.00                    12            1            4
                76.00                     4            1            5
               177.00                    19            2            1
               167.00                     7            2            2
                62.00                     8            2            3
                79.00                    10            2            4
               138.00                    12            2            5
                79.00                    13            3            1
               182.00                    18            3            2
               145.00                    13            3            3
               124.00                     8            3            4
               154.00                    14            4            1
                25.00                     4            4            2
               184.00                    21            4            3
               148.00                    14            4            4
                76.00                     8            4            5
                15.00                    14            5            1
                96.00                     9            5            2
                90.00                     3            5            3
                75.00                     7            5            4
               117.00                    11            5            5

--- 24 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>
>>--===========================================
>>-- Section query1 is done.
>>--===========================================
>>obey TEST002(match2);
>>--===========================================
>>-- Query 2, matching sumby_month_dep_state
>>--===========================================
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST002(query2);
>>prepare QueryStmt from
+>  select sum(fprice) total_price, 
+>         sum(dimdata) sumdata,
+>         fmonth omonth, fyear oyear,
+>         fdep odep, 
+>         fstate ostate
+>  from   T002_FACT, T002_DIM
+>  where  dimkey=dkey
+>  group by fmonth, fyear, fdep, fstate
+>  order by fmonth, fyear, fdep, fstate;

--- SQL command prepared.
>>
>>
>>set pattern $$QueryName$$ SUMBY_QUERY_MONTH_YEAR_DEP_STATE;
>>obey TEST002(dump_Query);
>>--===========================================
>>--== Create the query descriptor XML
>>--===========================================
>>
>>log $$QueryName$$.tmp clear;
>>sh sh ./CropDescriptor.ksh -q -i $$QueryName$$.TMP -o $$QueryName$$.xml;
>>-- and once for the test log file
>>execute QueryStmt;

Query Descriptor                                                      
----------------------------------------------------------------------

<Query>                                                               
  <Version>                                                           
    1.0                                                               
  </Version>                                                          
  <Misc rewriteLevel='3'>                                             
  </Misc>                                                             
  <JBB id='B0'>                                                       
    <Hub>                                                             
      <JBBCList>                                                      
        <Table id='T1' TS='212223873639188679' numCols='3'>           
          CAT.SUMBY.T002_DIM                                          
          <Key>                                                       
            <Column id='C21' tableId='T1' colIndex='0' isNullable='0'>
              CAT.SUMBY.T002_DIM.SYSKEY                               
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
        <Table id='T2' TS='212223873639188679' numCols='10'>          
          CAT.SUMBY.T002_FACT                                         
          <Key>                                                       
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'> 
              CAT.SUMBY.T002_FACT.SYSKEY                              
            </Column>                                                 
          </Key>                                                      
        </Table>                                                      
      </JBBCList>                                                     
      <JoinPredList>                                                  
        <JoinPred id='J58'>                                           
          <Column id='C22' tableId='T1' colIndex='1'>                 
            CAT.SUMBY.T002_DIM.DKEY                                   
          </Column>                                                   
          <Column id='C10' tableId='T2' colIndex='9'>                 
            CAT.SUMBY.T002_FACT.DIMKEY                                
          </Column>                                                   
        </JoinPred>                                                   
      </JoinPredList>                                                 
    </Hub>                                                            
    <ExtraHub>                                                        
    </ExtraHub>                                                       
    <OutputList>                                                      
      <Output id='O28'>                                               
        <Expr id='X28'>                                               
          <Function id='S28' op='sum' aggregateFunc='3'>              
            <Column id='C9' tableId='T2' colIndex='8'>                
              CAT.SUMBY.T002_FACT.FPRICE                              
            </Column>                                                 
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O29'>                                               
        <Expr id='X29'>                                               
          <Function id='S29' op='sum' aggregateFunc='3'>              
            <Column id='C23' tableId='T1' colIndex='2'>               
              CAT.SUMBY.T002_DIM.DIMDATA                              
            </Column>                                                 
          </Function>                                                 
        </Expr>                                                       
      </Output>                                                       
      <Output id='O37'>                                               
        <Column ref='C3'>                                             
        </Column>                                                     
      </Output>                                                       
      <Output id='O40'>                                               
        <Column ref='C4'>                                             
        </Column>                                                     
      </Output>                                                       
      <Output id='O46'>                                               
        <Column ref='C6'>                                             
        </Column>                                                     
      </Output>                                                       
      <Output id='O52'>                                               
        <Column ref='C8'>                                             
        </Column>                                                     
      </Output>                                                       
    </OutputList>                                                     
    <GroupBy id='G3'>                                                 
      <Primary>                                                       
        <Column id='C3' tableId='T2' colIndex='2'>                    
          CAT.SUMBY.T002_FACT.FMONTH                                  
        </Column>                                                     
        <Column id='C4' tableId='T2' colIndex='3'>                    
          CAT.SUMBY.T002_FACT.FYEAR                                   
        </Column>                                                     
        <Column id='C6' tableId='T2' colIndex='5'>                    
          CAT.SUMBY.T002_FACT.FDEP                                    
        </Column>                                                     
        <Column id='C8' tableId='T2' colIndex='7'>                    
          CAT.SUMBY.T002_FACT.FSTATE                                  
        </Column>                                                     
      </Primary>                                                      
    </GroupBy>                                                        
  </JBB>                                                              
</Query>                                                              

--- 93 row(s) selected.
>>
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

--- SQL operation complete.
>>
>>sh echo INITIALIZE                                 >> sumby2.in;
>>sh echo PUBLISH SUMBY_NONE.xml                     >> sumby2.in;
>>sh echo PUBLISH SUMBY_DAY.xml                      >> sumby2.in;
>>sh echo PUBLISH SUMBY_DAY_DEP.xml                  >> sumby2.in;
>>sh echo PUBLISH SUMBY_DAY_DEP_STATE.xml            >> sumby2.in;
>>sh echo PUBLISH SUMBY_DAY_DEP_STORE.xml            >> sumby2.in;
>>sh echo PUBLISH SUMBY_DAY_ITEM.xml                 >> sumby2.in;
>>sh echo PUBLISH SUMBY_DAY_ITEM_STATE.xml           >> sumby2.in;
>>sh echo PUBLISH SUMBY_DAY_ITEM_STORE.xml           >> sumby2.in;
>>sh echo PUBLISH SUMBY_DAY_STATE.xml                >> sumby2.in;
>>sh echo PUBLISH SUMBY_DAY_STORE.xml                >> sumby2.in;
>>sh echo PUBLISH SUMBY_DEP.xml                      >> sumby2.in;
>>sh echo PUBLISH SUMBY_ITEM.xml                     >> sumby2.in;
>>sh echo PUBLISH SUMBY_MONTH.xml                    >> sumby2.in;
>>sh echo PUBLISH SUMBY_MONTH_DEP.xml                >> sumby2.in;
>>sh echo PUBLISH SUMBY_MONTH_DEP_STATE.xml          >> sumby2.in;
>>sh echo PUBLISH SUMBY_MONTH_DEP_STORE.xml          >> sumby2.in;
>>sh echo PUBLISH SUMBY_MONTH_ITEM.xml               >> sumby2.in;
>>sh echo PUBLISH SUMBY_MONTH_ITEM_STATE.xml         >> sumby2.in;
>>sh echo PUBLISH SUMBY_MONTH_ITEM_STORE.xml         >> sumby2.in;
>>sh echo PUBLISH SUMBY_MONTH_STATE.xml              >> sumby2.in;
>>sh echo PUBLISH SUMBY_MONTH_STORE.xml              >> sumby2.in;
>>sh echo PUBLISH SUMBY_STATE.xml                    >> sumby2.in;
>>sh echo PUBLISH SUMBY_STORE.xml                    >> sumby2.in;
>>sh echo PUBLISH SUMBY_YEAR.xml                     >> sumby2.in;
>>sh echo PUBLISH SUMBY_YEAR_DEP.xml                 >> sumby2.in;
>>sh echo PUBLISH SUMBY_YEAR_DEP_STATE.xml           >> sumby2.in;
>>sh echo PUBLISH SUMBY_YEAR_DEP_STORE.xml           >> sumby2.in;
>>sh echo PUBLISH SUMBY_YEAR_ITEM.xml                >> sumby2.in;
>>sh echo PUBLISH SUMBY_YEAR_ITEM_STATE.xml          >> sumby2.in;
>>sh echo PUBLISH SUMBY_YEAR_ITEM_STORE.xml          >> sumby2.in;
>>sh echo PUBLISH SUMBY_YEAR_STATE.xml               >> sumby2.in;
>>sh echo PUBLISH SUMBY_YEAR_STORE.xml               >> sumby2.in;
>>sh echo MATCH SUMBY_QUERY_MONTH_YEAR_DEP_STATE.xml >> sumby2.in;
>>
>>-- Run QMS as a command line tool
>>sh sh -c "$QMS sumby2.in sumby.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table ref='T2'>
          CAT.SUMBY.T002_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_NONE
          </MVName>
          <OutputList>
            <Output ref='C9' result='Provided'>
              <MVColumn ref='C9'>
                FPRICE
              </MVColumn>
            </Output>
            <Output ref='C23' result='Provided'>
              <MVColumn ref='C23'>
                DIMDATA
              </MVColumn>
            </Output>
            <Output ref='O37' result='Provided'>
              <MVColumn ref='C3'>
                FMONTH
              </MVColumn>
            </Output>
            <Output ref='O40' result='Provided'>
              <MVColumn ref='C4'>
                FYEAR
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                FDEP
              </MVColumn>
            </Output>
            <Output ref='O52' result='Provided'>
              <MVColumn ref='C8'>
                FSTATE
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.SUMBY.T002_DIM
        </Table>
        <Table ref='T2'>
          CAT.SUMBY.T002_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_DAY_DEP_STATE
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
              <MVColumn ref='C8'>
                OSTATE
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O37' result='Provided'>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
            </Output>
            <Output ref='O40' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Output>
            <Output ref='O52' result='Provided'>
              <MVColumn ref='C8'>
                OSTATE
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S29'>
                    SUMDATA
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O28' result='NotProvided'>
              <Expr ref='X28'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S28'>
                    TOTAL_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_DAY_DEP_STORE
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
              <MVColumn ref='C8'>
                OSTATE
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O37' result='Provided'>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
            </Output>
            <Output ref='O40' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Output>
            <Output ref='O52' result='Provided'>
              <MVColumn ref='C8'>
                OSTATE
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S29'>
                    SUMDATA
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O28' result='NotProvided'>
              <Expr ref='X28'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S28'>
                    TOTAL_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_DAY_ITEM_STATE
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
              <MVColumn ref='C8'>
                OSTATE
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O37' result='Provided'>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
            </Output>
            <Output ref='O40' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Output>
            <Output ref='O52' result='Provided'>
              <MVColumn ref='C8'>
                OSTATE
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S29'>
                    SUMDATA
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O28' result='NotProvided'>
              <Expr ref='X28'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S28'>
                    TOTAL_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_DAY_ITEM_STORE
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
              <MVColumn ref='C8'>
                OSTATE
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O37' result='Provided'>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
            </Output>
            <Output ref='O40' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Output>
            <Output ref='O52' result='Provided'>
              <MVColumn ref='C8'>
                OSTATE
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S29'>
                    SUMDATA
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O28' result='NotProvided'>
              <Expr ref='X28'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S28'>
                    TOTAL_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_MONTH_DEP_STATE
          </MVName>
          <GroupBy ref='G3' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O28' result='Provided'>
              <MVColumn ref='X28'>
                TOTAL_PRICE
              </MVColumn>
            </Output>
            <Output ref='O29' result='Provided'>
              <MVColumn ref='X29'>
                SUMDATA
              </MVColumn>
            </Output>
            <Output ref='O37' result='Provided'>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
            </Output>
            <Output ref='O40' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Output>
            <Output ref='O52' result='Provided'>
              <MVColumn ref='C8'>
                OSTATE
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_MONTH_DEP_STORE
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
              <MVColumn ref='C8'>
                OSTATE
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O37' result='Provided'>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
            </Output>
            <Output ref='O40' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Output>
            <Output ref='O52' result='Provided'>
              <MVColumn ref='C8'>
                OSTATE
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S29'>
                    SUMDATA
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O28' result='NotProvided'>
              <Expr ref='X28'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S28'>
                    TOTAL_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_MONTH_ITEM_STATE
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
              <MVColumn ref='C8'>
                OSTATE
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O37' result='Provided'>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
            </Output>
            <Output ref='O40' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Output>
            <Output ref='O52' result='Provided'>
              <MVColumn ref='C8'>
                OSTATE
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S29'>
                    SUMDATA
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O28' result='NotProvided'>
              <Expr ref='X28'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S28'>
                    TOTAL_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='0'>
            CAT.SUMBY.SUMBY_MONTH_ITEM_STORE
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
            <Primary>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
              <MVColumn ref='C8'>
                OSTATE
              </MVColumn>
            </Primary>
          </GroupBy>
          <OutputList>
            <Output ref='O37' result='Provided'>
              <MVColumn ref='C3'>
                OMONTH
              </MVColumn>
            </Output>
            <Output ref='O40' result='Provided'>
              <MVColumn ref='C4'>
                OYEAR
              </MVColumn>
            </Output>
            <Output ref='O46' result='Provided'>
              <MVColumn ref='C6'>
                ODEP
              </MVColumn>
            </Output>
            <Output ref='O52' result='Provided'>
              <MVColumn ref='C8'>
                OSTATE
              </MVColumn>
            </Output>
            <Output ref='O29' result='NotProvided'>
              <Expr ref='X29'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S29'>
                    SUMDATA
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O28' result='NotProvided'>
              <Expr ref='X28'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S28'>
                    TOTAL_PRICE
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
  </JbbResult>
</Result>
>>
>>-- Verify the MV is picked by the optimizer.
>>set pattern $$QueryName$$ query2;
>>obey TEST002(compare);
>>
>>obey TEST002($$QueryName$$);
>>prepare QueryStmt from
+>  select sum(fprice) total_price, 
+>         sum(dimdata) sumdata,
+>         fmonth omonth, fyear oyear,
+>         fdep odep, 
+>         fstate ostate
+>  from   T002_FACT, T002_DIM
+>  where  dimkey=dkey
+>  group by fmonth, fyear, fdep, fstate
+>  order by fmonth, fyear, fdep, fstate;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.SUMBY.SUMBY_MONTH_ITEM_STATE                            

--- 1 row(s) selected.
>>
>>execute QueryStmt;

TOTAL_PRICE            SUMDATA               OMONTH       OYEAR        ODEP         OSTATE
---------------------  --------------------  -----------  -----------  -----------  -----------

                83.00                     8            1         2010            4            4
                67.00                     3            1         2010            5            4
                12.00                     2            1         2011            5            4
                71.00                     9            1         2012            5            2
                49.00                     6            2         2012            1            1
               165.00                     6            2         2012            2            3
                65.00                     6            2         2012            4            3
                39.00                     6            3         2010            1            4
                66.00                    15            3         2012            1            4
                 7.00                     5            3         2012            3            5
                67.00                     9            4         2010            1            2
                56.00                     5            4         2010            3            2
                25.00                     4            4         2012            2            1
                32.00                     8            4         2012            2            3
                61.00                     5            4         2012            5            2
                40.00                     2            5         2010            4            4
                44.00                     8            5         2012            5            1
                66.00                     7            6         2011            4            2
                73.00                     5            6         2011            4            4
                61.00                     1            6         2011            5            2
                76.00                     4            6         2011            5            4
                51.00                     5            6         2012            2            1
               101.00                     7            6         2012            3            3
                63.00                     6            7         2010            2            1
                50.00                     8            7         2010            3            2
                90.00                     3            7         2010            3            4
                75.00                     7            7         2011            4            5
                15.00                     3            7         2011            5            1
                53.00                     5            7         2012            1            3
                14.00                     4            7         2012            4            5
                 4.00                     3            8         2010            1            3
                87.00                     3            8         2011            4            4
                58.00                     1            8         2012            4            1
                 8.00                     1            9         2010            1            4
                45.00                     4            9         2010            2            3
                62.00                     8            9         2010            3            5
                39.00                     8           10         2010            4            1
                66.00                     6           10         2011            1            1
                27.00                     9           11         2010            3            5
                88.00                     8           11         2011            1            3
                52.00                     9           12         2010            2            5
                69.00                     4           12         2011            2            3
                59.00                     7           12         2011            3            1
                95.00                     5           12         2011            3            3
                 4.00                     6           12         2012            1            1
                20.00                     1           12         2012            1            3

--- 46 row(s) selected.
>>
>>log;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST002($$QueryName$$);
>>prepare QueryStmt from
+>  select sum(fprice) total_price, 
+>         sum(dimdata) sumdata,
+>         fmonth omonth, fyear oyear,
+>         fdep odep, 
+>         fstate ostate
+>  from   T002_FACT, T002_DIM
+>  where  dimkey=dkey
+>  group by fmonth, fyear, fdep, fstate
+>  order by fmonth, fyear, fdep, fstate;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.SUMBY.T002_DIM                                          
FILE_SCAN                       CAT.SUMBY.T002_FACT                                         

--- 2 row(s) selected.
>>
>>execute QueryStmt;

TOTAL_PRICE            SUMDATA               OMONTH       OYEAR        ODEP         OSTATE
---------------------  --------------------  -----------  -----------  -----------  -----------

                83.00                     8            1         2010            4            4
                67.00                     3            1         2010            5            4
                12.00                     2            1         2011            5            4
                71.00                     9            1         2012            5            2
                49.00                     6            2         2012            1            1
               165.00                     6            2         2012            2            3
                65.00                     6            2         2012            4            3
                39.00                     6            3         2010            1            4
                66.00                    15            3         2012            1            4
                 7.00                     5            3         2012            3            5
                67.00                     9            4         2010            1            2
                56.00                     5            4         2010            3            2
                25.00                     4            4         2012            2            1
                32.00                     8            4         2012            2            3
                61.00                     5            4         2012            5            2
                40.00                     2            5         2010            4            4
                44.00                     8            5         2012            5            1
                66.00                     7            6         2011            4            2
                73.00                     5            6         2011            4            4
                61.00                     1            6         2011            5            2
                76.00                     4            6         2011            5            4
                51.00                     5            6         2012            2            1
               101.00                     7            6         2012            3            3
                63.00                     6            7         2010            2            1
                50.00                     8            7         2010            3            2
                90.00                     3            7         2010            3            4
                75.00                     7            7         2011            4            5
                15.00                     3            7         2011            5            1
                53.00                     5            7         2012            1            3
                14.00                     4            7         2012            4            5
                 4.00                     3            8         2010            1            3
                87.00                     3            8         2011            4            4
                58.00                     1            8         2012            4            1
                 8.00                     1            9         2010            1            4
                45.00                     4            9         2010            2            3
                62.00                     8            9         2010            3            5
                39.00                     8           10         2010            4            1
                66.00                     6           10         2011            1            1
                27.00                     9           11         2010            3            5
                88.00                     8           11         2011            1            3
                52.00                     9           12         2010            2            5
                69.00                     4           12         2011            2            3
                59.00                     7           12         2011            3            1
                95.00                     5           12         2011            3            3
                 4.00                     6           12         2012            1            1
                20.00                     1           12         2012            1            3

--- 46 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>
>>--===========================================
>>-- Section query1 is done.
>>--===========================================
>>obey TEST002(clean_up);
>>----------------------------------------------------
>>------------------ clean up section ----------------
>>----------------------------------------------------
>>drop schema sumby cascade;

--- SQL operation complete.
>>
>>exit;

End of MXCI Session

