Rem
Rem $Header: onevdcat.sql 24-jul-2000.17:12:45 dthompso Exp $
Rem
Rem onevdcat.sql
Rem
Rem  Copyright (c) Oracle Corporation 2000. All Rights Reserved.
Rem
Rem    NAME
Rem      onevdcat.sql - dba$olap_catalogs
Rem
Rem    DESCRIPTION
Rem      Catalogs used to categorize measures.
Rem
Rem    NOTES
Rem      
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    dthompso    07/24/00 - correct name.
Rem    dthompso    07/19/00 - use correct classificationentry
Rem    dthompso    04/26/00 - Initial Version
Rem    dthompso    01/00/00 - Created
Rem
  
CREATE OR REPLACE VIEW dba$olap_catalogs AS SELECT 
  c.irid catalog_id
, c.name catalog_name
, cp.irid parent_catalog_id
, c.description description
FROM
  cwm$classification c
, cwm$classification cp
, cwm$classificationtype cty
, cwm$classificationentry ce
WHERE cty.irid = c.classificationtype_irid
AND cty.name = 'ORACLE_OLAP_CATALOG'
AND c.irid = ce.element_irid
AND ce.name = 'CATALOG'    
AND ce.classification_irid = cp.irid 
UNION ALL SELECT
  c.irid catalog_id
, c.name catalog_name
, null parent_catalog_id
, c.description description
FROM
  cwm$classification c
, cwm$classificationtype cty
WHERE cty.irid = c.classificationtype_irid
AND cty.name = 'ORACLE_OLAP_CATALOG'
AND NOT EXISTS
    (SELECT null 
    FROM cwm$classificationentry ce
    WHERE ce.name = 'CATALOG'
    AND ce.element_irid = c.irid)  
WITH READ ONLY
/