ITPub博客

首页 > 数据库 > Oracle > Oracle Discoverer中一些有用的SQL

Oracle Discoverer中一些有用的SQL

原创 Oracle 作者:zhyuh 时间:2005-02-25 17:40:18 0 删除 编辑
正确使用Oracle Discoverer的数据字典有时候能极大提高工作效率,可是找不到关于Discoverer数据字典的资料,有资料估计也很难自己把它掌握。那就有多少是多少吧,工作中用到过的SQL贴到这里,今后用到时好找。 [@more@]

 

Business areaFolder

以下sql显示所有的business area,下属的folderfolder的描述信息

select eb.ba_name
     , eo.obj_name
       , eo.obj_description
from   eul4_ba_obj_links ebol
     , eul4_bas eb
       , eul4_objs eo
where  ebol.bol_ba_id  = eb.ba_id
and    ebol.bol_obj_id = eo.obj_id 
order by ba_name

 

BA_NAME

OBJ_NAME

OBJ_DESCRIPTION

ADMIN

ADM_BA_DOC

 

ADMIN

ADM_FOLD_DOC

 

ADMIN

ADM_JOIN_DETAIL_DOC

 

ADMIN

ADM_REFRESH_LOG_STATUS

ADM_REFRESH_LOG

ADMIN

ADM_JOIN_DOC

 

 

Folder Item

以下sql显示所有folder和下属的item信息,并用seq显示其层次

select 1 as seq
     , eo.obj_id as id
     , eo.obj_name as name
from   eul4_objs eo
union
select
2 as seq
     , ee.it_obj_id as id
     ,
'     ' || ee.exp_name as name
from   eul4_expressions ee
order by id, seq

 

SEQ

ID

NAME

1

100057

ADM_BA_DOC

2

100057

     BA_NAME

2

100057

     OBJ_DESCRIPTION

2

100057

     OBJ_NAME

1

100058

ADM_FOLD_DOC

2

100058

     DESCRIPTION

2

100058

     ID

2

100058

     NAME

2

100058

     SEQ

1

100059

ADM_JOIN_DETAIL_DOC

2

100059

     DETAIL

2

100059

     JOIN_ID

2

100059

     MASTER

2

100059

     NAME

1

100060

ADM_JOIN_DOC

2

100060

     JOIN_DESC

2

100060

     JOIN_ID

2

100060

     ORD_ID

 

 

Join

一下sql显示Join详细信息,包括join名,master folder, detail folder,以及join所用的item

select ekc.key_id as join_id
     , ekc.key_name ||
'  [ Master: ' || eor.obj_name || ' ;  Detail: ' || eo.obj_name  || ']' as join_desc
       ,
1 as ord_id
from   eul4_key_cons ekc
     , eul4_objs eo
       , eul4_objs eor
where  ekc.key_obj_id = eo.obj_id
and    ekc.fk_obj_id_remote = eor.obj_id
union
select ee.jp_key_id as join_id
     ,
'    ' || eod.obj_name || '.' || eee.exp_name as join_desc
       ,
2 as ord_id
from   eul4_expressions ee
     , eul4_exp_deps    eed
       , eul4_objs        eod
       , eul4_expressions eee
where  eed.pd_p_id   = ee.exp_id
and    eee.exp_id    = eed.ped_exp_id
and    eee.it_obj_id = eod.obj_id
order by join_id
      , ord_id


JOIN_ID

JOIN_DESC

ORD_ID

101631

FR_COST_CENT -> FR_CSTM_CNCT  [ Master: FR_COST_CENT ;  Detail: FR_CSTM_CNCT]

1

101631

    FR_COST_CENT.COST_CENT_ID

2

101631

    FR_COST_CENT.CSTM_CMPY_OR_PRSN_ID

2

......

  ......

.

Oracle Discoverer相关文章:

http://blog.itpub.net/post/334/18294

http://blog.itpub.net/post/334/12966

http://blog.itpub.net/post/334/6813

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/207/viewspace-791085/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2008719