问题：

EDENFR用户已经配置好所有的Discoverer Objects(包括Business Area, Folder, Join等)，以此为基础，将所有Discoverer Objects复制到EDENUK用户下，一般采用exp/imp Business Area然后修改属性的方法。在给EDENUK修改属性和配置过程中，经常由于各种原因会出现各种改动。

现在客户要求以EDENFR的joins为标准，检查EDENUK中所有joins，并列出EDENUK中所有不一致的joins。类似的检查也要在EDENES,EDENDE等多个用户下进行。

打开Discoverer administrator，发现EDENFR下的joins有一百多个，如果逐条检查，工作量将非常巨大。

方法：

eul4_key_cons.key_name纪录了该EUL中所有的join名字，而且Discoverer中join的命名有如下规则：From_Folder_name -> To_Folder_name, 比如：

FR_##_CSTM -> FR_??_LIVE_VHC
FR_##_CSTM -> FR_??_LIVE_VHC 2
FR_##_CSTM -> FR_COST_CENT

以下SQL能返回join ID, join name, master folder(from folder), detail folder(to folder)信息：

SQL>select ekc.key_id as join_id
     , ekc.key_name || '  [ Master: ' || eor.obj_name || ' ;  Detail: ' || eo.obj_name  || ']' as join_desc
 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

 JOIN_ID JOIN_DESC
1 101685 FR_SLS_QTTN -> FR_CSTM_ORD_HDR  [ Master: FR_SLS_QTTN ;  Detail: FR_CSTM_ORD_HDR]
2 101697 FR_SLS_QTTN -> FR_DRV  [ Master: FR_SLS_QTTN ;  Detail: FR_DRV]
3 101701 FR_SLS_QTTN -> FR_SLS_QTTN_2  [ Master: FR_SLS_QTTN ;  Detail: FR_SLS_QTTN_2]
4 101713 FR_SLS_QTTN -> FR_SLS_QTTN_ITEM  [ Master: FR_SLS_QTTN ;  Detail: FR_SLS_QTTN_ITEM]

检查过程：

根据以上规律和信息，可以用下面方法快速检查

1. 创建表，存储EDENFR的 join / join from table / join to table 信息
create table tmp_join_fr
as
select ekc.key_name join_name,
       substr(ekc.key_name, 4, instr(ekc.key_name, '->', 1, 1) - 5) fromcol,
       substr(ekc.key_name, instr(ekc.key_name, '->', 1, 1) + 6) tocol
  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
   and ekc.key_name like 'FR_%'

2. 创建表，存储EDENUK join / join from table / join to table 信息  
create table tmp_join_uk
as
select ekc.key_name join_name,
       substr(ekc.key_name, 4, instr(ekc.key_name, '->', 1, 1) - 5) fromcol,
       substr(ekc.key_name, instr(ekc.key_name, '->', 1, 1) + 6) tocol
  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
   and ekc.key_name like 'UK_%'

3. 用以下SQL检查EDENFR中有而EDENUK中缺失的join
select * from tmp_join_fr where fromcol||tocol not in (select fromcol||tocol from tmp_join_uk)  

 用以下SQL检查EDENFR中没有有而EDENUK中有的join

select * from tmp_join_uk where fromcol||tocol not in (select fromcol||tocol from tmp_join_fr)