Oracle Discoverer中一些有用的SQL

正确使用Oracle Discoverer的数据字典有时候能极大提高工作效率，可是找不到关于Discoverer数据字典的资料，有资料估计也很难自己把它掌握。那就有多少是多少吧，工作中用到过的SQL贴到这里，今后用到时好找。 [@more@]

Business area和Folder

以下sql显示所有的business area，下属的folder和folder的描述信息

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/207/viewspace-791085/