VIEW: SYS.KU$_CUBE_TAB_VIEW

Source

View Text - Preformatted

select ot.obj#, aw.awname, ot.flags,
    cast( multiset(select ofv.obj#, ofv.colname, ofv.objname,
                    (select ol.objname                     /*dimusing*/
                     from ku$_cube_fact_view ol
                     where ol.obj#=ot.obj#
                     and ol.coltype=10
                     and ol.pcol#=ofv.col#),
                    cast( multiset(select ol.obj#,              /*gid*/
                                          ol.colname, ol.pcolname, null,
                                          ol.objname, ol.qdr,
                                          ol.qdrval, ol.flags
                                     from ku$_cube_fact_view ol
                                     where ol.obj#=ot.obj#
                                     and ol.coltype=5
                                     and ol.pcol#=ofv.col#
                                   ) as ku$_cube_fact_list_t
                        ),
                    cast( multiset(select ol.obj#,              /*pgid*/
                                          ol.colname, ol.pcolname, null,
                                          ol.objname, ol.qdr,
                                          ol.qdrval, ol.flags
                                     from ku$_cube_fact_view ol
                                     where ol.obj#=ot.obj#
                                     and ol.coltype=6
                                     and ol.pcol#=ofv.col#
                                  ) as ku$_cube_fact_list_t
                        ),
                    cast( multiset(select ol.obj#,              /*attr*/
                                          ol.colname, ol.pcolname, null,
                                          ol.objname, ol.qdr,
                                          ol.qdrval, ol.flags
                                     from ku$_cube_fact_view ol
                                     where ol.obj#=ot.obj#
                                     and ol.coltype=4
                                     and ol.pcol#=ofv.col#
                                   ) as ku$_cube_fact_list_t
                        ),
                    cast( multiset(select ol.obj#,              /*lvls*/
                                          ol.colname, ol.pcolname, null,
                                          ol.objname, ol.qdr,
                                          ol.qdrval, ol.flags
                                     from ku$_cube_fact_view ol
                                     where ol.obj#=ot.obj#
                                     and ol.coltype=3
                                     and ol.pcol#=ofv.col#
                                  ) as ku$_cube_fact_list_t
                        ),
                    cast( multiset(select ol.obj#, ol.objname, ol.qdr,
                                          ol.qdrval,
                                          cast( multiset(select
                                                           lvl.obj#,
                                                           lvl.colname,
                                                           lvl.pcolname,
                                                           null,
                                                           lvl.objname,
                                                           lvl.qdr,
                                                           lvl.qdrval,
                                                           oh.flags
                                            from ku$_cube_fact_view lvl,
                                                 sys.olap_tab_hier$ oh
                                            where oh.hier#=ol.hier#
                                              and oh.obj#=ol.obj#
                                              and lvl.obj#=oh.obj#
                                              and (lvl.pcol#=ofv.col#
                                               or (lvl.col#=ofv.col#
                                               and (oh.flags=1)))
                                              and lvl.colname=
                           (select col.name from sys.col$ col
                              where col.obj#=oh.obj# and col.col#=oh.col#)
                                            order by oh.FLAGS desc, oh.ord
                                                ) as ku$_cube_fact_list_t
                                              ),
                                cast(multiset(select inh.obj#,  /* inhier */
                                                     inh.colname, inh.pcolname,
                                                     null, inh.objname,
                                                     inh.qdr, inh.qdrval,
                                                     inh.flags
                                               from ku$_cube_fact_view inh
                                               where inh.obj#=ot.obj#
                                                 and inh.coltype=11
                                                 and inh.hier#=ol.hier#
                                                 and inh.pcol#=ofv.col#
                                               ) as ku$_cube_fact_list_t
                                        ),
                                          ol.flags
                                     from ku$_cube_fact_view ol
                                     where ol.obj#=ot.obj#
                                     and ol.coltype=7
                                     and ol.pcol#=ofv.col#
                                   ) as ku$_cube_hier_list_t
                        ),
                    ofv.flags /* flags */
                    from ku$_cube_fact_view ofv
                    where ofv.coltype=2 and ofv.obj#=ot.obj#
                  ) as ku$_cube_dim_list_t
        ),
    /* FACTs */
    cast( multiset(select ofv.obj#, ofv.colname, ofv.pcolname,
                          (select col.name from sys.col$ col        /* COUNT */
                             where col.obj#=ofv.obj# and col.col#=
                               (select otc.col# from olap_tab_col$ otc
                                  where otc.obj#=ofv.obj# and
                                        otc.pcol#=ofv.col# and
                                        otc.coltype=9)),
                          ofv.objname, ofv.qdr, ofv.qdrval, ofv.flags
                     from ku$_cube_fact_view ofv
                     where ofv.coltype=1 and ofv.obj#=ot.obj#
                  ) as ku$_cube_fact_list_t
        ),
    /* CGID */
    cast( multiset(select ofv.obj#, ofv.colname, ofv.pcolname, null,
                          ofv.objname, ofv.qdr, ofv.qdrval, ofv.flags
                     from ku$_cube_fact_view ofv
                     where ofv.coltype=8 and ofv.obj#=ot.obj#
                     order by ofv.col#
                  ) as ku$_cube_fact_list_t
        )
  from sys.olap_tab$ ot, sys.aw$ aw
    where aw.awseq#=ot.awseq#

View Text - HTML Formatted

SELECT OT.OBJ#
, AW.AWNAME
, OT.FLAGS
, CAST( MULTISET(SELECT OFV.OBJ#
, OFV.COLNAME
, OFV.OBJNAME
, (SELECT OL.OBJNAME /*DIMUSING*/
FROM KU$_CUBE_FACT_VIEW OL
WHERE OL.OBJ#=OT.OBJ#
AND OL.COLTYPE=10
AND OL.PCOL#=OFV.COL#)
, CAST( MULTISET(SELECT OL.OBJ#
, /*GID*/ OL.COLNAME
, OL.PCOLNAME
, NULL
, OL.OBJNAME
, OL.QDR
, OL.QDRVAL
, OL.FLAGS
FROM KU$_CUBE_FACT_VIEW OL
WHERE OL.OBJ#=OT.OBJ#
AND OL.COLTYPE=5
AND OL.PCOL#=OFV.COL# ) AS KU$_CUBE_FACT_LIST_T )
, CAST( MULTISET(SELECT OL.OBJ#
, /*PGID*/ OL.COLNAME
, OL.PCOLNAME
, NULL
, OL.OBJNAME
, OL.QDR
, OL.QDRVAL
, OL.FLAGS
FROM KU$_CUBE_FACT_VIEW OL
WHERE OL.OBJ#=OT.OBJ#
AND OL.COLTYPE=6
AND OL.PCOL#=OFV.COL# ) AS KU$_CUBE_FACT_LIST_T )
, CAST( MULTISET(SELECT OL.OBJ#
, /*ATTR*/ OL.COLNAME
, OL.PCOLNAME
, NULL
, OL.OBJNAME
, OL.QDR
, OL.QDRVAL
, OL.FLAGS
FROM KU$_CUBE_FACT_VIEW OL
WHERE OL.OBJ#=OT.OBJ#
AND OL.COLTYPE=4
AND OL.PCOL#=OFV.COL# ) AS KU$_CUBE_FACT_LIST_T )
, CAST( MULTISET(SELECT OL.OBJ#
, /*LVLS*/ OL.COLNAME
, OL.PCOLNAME
, NULL
, OL.OBJNAME
, OL.QDR
, OL.QDRVAL
, OL.FLAGS
FROM KU$_CUBE_FACT_VIEW OL
WHERE OL.OBJ#=OT.OBJ#
AND OL.COLTYPE=3
AND OL.PCOL#=OFV.COL# ) AS KU$_CUBE_FACT_LIST_T )
, CAST( MULTISET(SELECT OL.OBJ#
, OL.OBJNAME
, OL.QDR
, OL.QDRVAL
, CAST( MULTISET(SELECT LVL.OBJ#
, LVL.COLNAME
, LVL.PCOLNAME
, NULL
, LVL.OBJNAME
, LVL.QDR
, LVL.QDRVAL
, OH.FLAGS
FROM KU$_CUBE_FACT_VIEW LVL
, SYS.OLAP_TAB_HIER$ OH
WHERE OH.HIER#=OL.HIER#
AND OH.OBJ#=OL.OBJ#
AND LVL.OBJ#=OH.OBJ#
AND (LVL.PCOL#=OFV.COL# OR (LVL.COL#=OFV.COL#
AND (OH.FLAGS=1)))
AND LVL.COLNAME= (SELECT COL.NAME
FROM SYS.COL$ COL
WHERE COL.OBJ#=OH.OBJ#
AND COL.COL#=OH.COL#) ORDER BY OH.FLAGS DESC
, OH.ORD ) AS KU$_CUBE_FACT_LIST_T )
, CAST(MULTISET(SELECT INH.OBJ#
, /* INHIER */ INH.COLNAME
, INH.PCOLNAME
, NULL
, INH.OBJNAME
, INH.QDR
, INH.QDRVAL
, INH.FLAGS
FROM KU$_CUBE_FACT_VIEW INH
WHERE INH.OBJ#=OT.OBJ#
AND INH.COLTYPE=11
AND INH.HIER#=OL.HIER#
AND INH.PCOL#=OFV.COL# ) AS KU$_CUBE_FACT_LIST_T )
, OL.FLAGS
FROM KU$_CUBE_FACT_VIEW OL
WHERE OL.OBJ#=OT.OBJ#
AND OL.COLTYPE=7
AND OL.PCOL#=OFV.COL# ) AS KU$_CUBE_HIER_LIST_T )
, OFV.FLAGS /* FLAGS */
FROM KU$_CUBE_FACT_VIEW OFV
WHERE OFV.COLTYPE=2
AND OFV.OBJ#=OT.OBJ# ) AS KU$_CUBE_DIM_LIST_T )
, /* FACTS */ CAST( MULTISET(SELECT OFV.OBJ#
, OFV.COLNAME
, OFV.PCOLNAME
, (SELECT COL.NAME
FROM SYS.COL$ COL /* COUNT */
WHERE COL.OBJ#=OFV.OBJ#
AND COL.COL#= (SELECT OTC.COL#
FROM OLAP_TAB_COL$ OTC
WHERE OTC.OBJ#=OFV.OBJ# AND OTC.PCOL#=OFV.COL# AND OTC.COLTYPE=9))
, OFV.OBJNAME
, OFV.QDR
, OFV.QDRVAL
, OFV.FLAGS
FROM KU$_CUBE_FACT_VIEW OFV
WHERE OFV.COLTYPE=1
AND OFV.OBJ#=OT.OBJ# ) AS KU$_CUBE_FACT_LIST_T )
, /* CGID */ CAST( MULTISET(SELECT OFV.OBJ#
, OFV.COLNAME
, OFV.PCOLNAME
, NULL
, OFV.OBJNAME
, OFV.QDR
, OFV.QDRVAL
, OFV.FLAGS
FROM KU$_CUBE_FACT_VIEW OFV
WHERE OFV.COLTYPE=8
AND OFV.OBJ#=OT.OBJ# ORDER BY OFV.COL# ) AS KU$_CUBE_FACT_LIST_T )
FROM SYS.OLAP_TAB$ OT
, SYS.AW$ AW
WHERE AW.AWSEQ#=OT.AWSEQ#