VIEW: SYS.ALL$OLAP2_AWS

Source

View Text - Preformatted

select aws11g.owner, aws11g.aw_name, aws11g.aw_number, aws11g.aw_version, '11.1' sf_version
  from all_aws aws11g,
            (select /*+ ordered */ a.awseq#, r.rid, p.rowid, p.propname
              from sys.aw$ a, sys.aw_prop$ p, sys.obj$ o, dba_users u,
                   (select max(rowid) keep (dense_rank last order by gen#) rid
                    from sys.aw_prop$ group by awseq#, oid, propname) r
              where a.owner# = u.user_id
                and o.owner# = a.owner#
                and o.name = 'AW$' || a.awname and o.type# = 2
                and a.awseq# = p.awseq#
                and p.objname = '___AW_VERSION'
                and p.propname  = 'AW$VERSION11.1'
                and p.rowid = r.rid
                and p.propval is not null) props11g
 where props11g.awseq# = aws11g.aw_number
union all
  select max(aws.owner) owner, max(aws.aw_name) aw, props1.awseq#,
         max(aws.aw_version) aw_version,
               (case when count(props1.awseq#) = 2 then '10.2'
                     when count(props1.awseq#) = 1 then '10.1.0.3'
                     else null end) sf_version
         from all_aws aws,
              (select /*+ ordered */ a.awseq#, r.rid, p.rowid, p.propname
               from sys.aw$ a, sys.aw_prop$ p, sys.obj$ o, dba_users u,
                    (select max(rowid) keep (dense_rank last order by gen#) rid
                     from sys.aw_prop$ group by awseq#, oid, propname) r
               where a.owner# = u.user_id
                 and o.owner# = a.owner#
                 and o.name = 'AW$' || a.awname and o.type# = 2
                 and a.awseq# = p.awseq#
                 and p.propname in ('AW$VERSION10.2', 'AW$VERSION10.1.0.3')
                 and p.objname = '___XML_USER_AW_VERSION'
                 and p.rowid = r.rid
                 and p.propval is not null) props1
  where props1.awseq# = aws.aw_number
        and (props1.awseq# not in
              (select /*+ ordered */ a.awseq#
               from sys.aw$ a, sys.aw_prop$ p, sys.obj$ o, dba_users u,
                    (select max(rowid) keep (dense_rank last order by gen#) rid
                     from sys.aw_prop$ group by awseq#, oid, propname) r
               where a.owner# = u.user_id
                 and o.owner# = a.owner#
                 and o.name = 'AW$' || a.awname and o.type# = 2
                 and a.awseq# = p.awseq#
                 and p.propname = 'AW$VERSION11.1'
                 and p.objname = '___AW_VERSION'
                 and p.rowid = r.rid
                 and p.propval is not null))
  group by awseq#

View Text - HTML Formatted

SELECT AWS11G.OWNER
, AWS11G.AW_NAME
, AWS11G.AW_NUMBER
, AWS11G.AW_VERSION
, '11.1' SF_VERSION
FROM ALL_AWS AWS11G
, (SELECT /*+ ORDERED */ A.AWSEQ#
, R.RID
, P.ROWID
, P.PROPNAME
FROM SYS.AW$ A
, SYS.AW_PROP$ P
, SYS.OBJ$ O
, DBA_USERS U
, (SELECT MAX(ROWID) KEEP (DENSE_RANK LAST ORDER BY GEN#) RID
FROM SYS.AW_PROP$ GROUP BY AWSEQ#
, OID
, PROPNAME) R
WHERE A.OWNER# = U.USER_ID
AND O.OWNER# = A.OWNER#
AND O.NAME = 'AW$' || A.AWNAME
AND O.TYPE# = 2
AND A.AWSEQ# = P.AWSEQ#
AND P.OBJNAME = '___AW_VERSION'
AND P.PROPNAME = 'AW$VERSION11.1'
AND P.ROWID = R.RID
AND P.PROPVAL IS NOT NULL) PROPS11G
WHERE PROPS11G.AWSEQ# = AWS11G.AW_NUMBER UNION ALL SELECT MAX(AWS.OWNER) OWNER
, MAX(AWS.AW_NAME) AW
, PROPS1.AWSEQ#
, MAX(AWS.AW_VERSION) AW_VERSION
, (CASE WHEN COUNT(PROPS1.AWSEQ#) = 2 THEN '10.2' WHEN COUNT(PROPS1.AWSEQ#) = 1 THEN '10.1.0.3' ELSE NULL END) SF_VERSION
FROM ALL_AWS AWS
, (SELECT /*+ ORDERED */ A.AWSEQ#
, R.RID
, P.ROWID
, P.PROPNAME
FROM SYS.AW$ A
, SYS.AW_PROP$ P
, SYS.OBJ$ O
, DBA_USERS U
, (SELECT MAX(ROWID) KEEP (DENSE_RANK LAST ORDER BY GEN#) RID
FROM SYS.AW_PROP$ GROUP BY AWSEQ#
, OID
, PROPNAME) R
WHERE A.OWNER# = U.USER_ID
AND O.OWNER# = A.OWNER#
AND O.NAME = 'AW$' || A.AWNAME
AND O.TYPE# = 2
AND A.AWSEQ# = P.AWSEQ#
AND P.PROPNAME IN ('AW$VERSION10.2'
, 'AW$VERSION10.1.0.3')
AND P.OBJNAME = '___XML_USER_AW_VERSION'
AND P.ROWID = R.RID
AND P.PROPVAL IS NOT NULL) PROPS1
WHERE PROPS1.AWSEQ# = AWS.AW_NUMBER
AND (PROPS1.AWSEQ# NOT IN (SELECT /*+ ORDERED */ A.AWSEQ#
FROM SYS.AW$ A
, SYS.AW_PROP$ P
, SYS.OBJ$ O
, DBA_USERS U
, (SELECT MAX(ROWID) KEEP (DENSE_RANK LAST ORDER BY GEN#) RID
FROM SYS.AW_PROP$ GROUP BY AWSEQ#
, OID
, PROPNAME) R
WHERE A.OWNER# = U.USER_ID
AND O.OWNER# = A.OWNER#
AND O.NAME = 'AW$' || A.AWNAME
AND O.TYPE# = 2
AND A.AWSEQ# = P.AWSEQ#
AND P.PROPNAME = 'AW$VERSION11.1'
AND P.OBJNAME = '___AW_VERSION'
AND P.ROWID = R.RID
AND P.PROPVAL IS NOT NULL)) GROUP BY AWSEQ#