3 Replies Latest reply: Apr 22, 2013 4:18 AM by 987852 RSS

    List Joins of all Folders

    987852
      Hi!

      Within my Business Area I have several Folders which have joins defined amongst each other.
      I would like to list those joins, eg. KEY_NAME, KEY_DESCRIPTION, what kind of join and most importantly the exact join formula like for example MYSCHEMA.TABLE_A.PK_A_ID = MYSCHEMA.TABLE_B.FK_A_ID.

      How can I do this by querying the EUL5-tables? I already saw that EUL5_KEY_CONS is part of this, but can't figure out how to get what I need.
      Any idea is very much appreciated!

      Cheers
        • 1. Re: List Joins of all Folders
          user13716376
          There is a workbook that is shipped with Discoverer (EUL Data Definition) that had a worsheet (Joins) that gives you pretty good info.
          It has page items to select Business Area and what type of join
          • 2. Re: List Joins of all Folders
            user7417334
            SELECT ba_from.ba_name From_Business_Area_Name
            ,j_from.obj_name From_Folder
            ,j_to.obj_name To_Folder
            ,ba_to.ba_name To_Business_Area_Name
            ,ekc.key_name Join_Name
            ,decode(ekc.fk_one_to_one,0,'One to Many',1,'One to One','None') RelationShip
            ,decode(ekc.fk_mstr_no_detail,0,'Equi Join',1,'Outer Join','None') JoinToDetail
            FROM EUL5.EUL5_KEY_CONS EKC
            , EUL5.EUL5_OBJS J_FROM
            , EUL5.EUL5_OBJS J_TO
            , EUL5.EUL5_bas ba_from
            , EUL5.EUL5_ba_obj_links bol
            , EUL5.EUL5_bas ba_to
            , EUL5.EUL5_ba_obj_links bol2
            Where 1=1
            AND ekc.key_obj_id = j_to.obj_id
            AND ekc.fk_obj_id_remote = j_from.obj_id
            AND ba_from.ba_id = bol.bol_ba_id (+)
            AND bol.bol_obj_id = j_from.obj_id (+)
            AND ba_to.ba_id = bol2.bol_ba_id (+)
            AND bol2.bol_obj_id = j_to.obj_id (+)
            • 3. Re: List Joins of all Folders
              987852
              thanks!

              i tweaked it a little and finally got this:

              SELECT
              KEYS.KEY_NAME Join_Name,
              KEYS.key_description Join_Desc,
              x1.ba as "Master_Business_Area",
              MST_FOLD.OBJ_NAME Master_Folder,
              MST_ITEM.EXP_NAME Master_Item,
              x1.col as "Master_Column",
              x2.ba as "Detail_Business_Area",
              DTL_FOLD.OBJ_NAME Detail_Folder,
              DTL_ITEM.EXP_NAME Detail_Item,
              x2.col as "Detail_Column",
              decode(KEYS.fk_one_to_one,0,'One to Many',1,'One to One','None') RelationShip
              ,decode(KEYS.fk_mstr_no_detail,0,'Equi Join',1,'Outer Join','None') JoinToDetail
              ,decode(KEYS.fk_dtl_no_master,0,'No',1,'Yes','None') OuterJoinOnMaster
              ,decode(KEYS.fk_mandatory,0,'No',1,'Yes','None') MasterMustExist
              FROM
              EUL5_KEY_CONS KEYS,
              EUL5_EXPRESSIONS JOIN_LINK,
              EUL5_EXPRESSIONS MST_ITEM,
              EUL5_EXPRESSIONS DTL_ITEM,
              EUL5_OBJS MST_FOLD,
              EUL5_OBJS DTL_FOLD,
              (SELECT
              bas.ba_name as ba,
              obj.obj_name as f,
              CASE WHEN obj.obj_ext_owner IS NOT NULL
              THEN obj.obj_ext_owner || '.' || obj.sobj_ext_table
              ELSE NULL
              END as "Table",
              ex.it_heading as "Element",
              CASE
              WHEN obj.obj_ext_owner IS NOT NULL AND ex.it_ext_column IS NOT NULL
              THEN obj.obj_ext_owner || '.' || obj.sobj_ext_table || '.' || ex.it_ext_column
              ELSE NULL
              END as col,
              ex.it_ext_column,
              ex.exp_type,
              ex.exp_id,
              ex.exp_formula1 as exp_formula1
              FROM
              eul5_bas bas,
              eul5_ba_obj_links bal,
              eul5_objs obj,
              eul5_expressions ex
              WHERE
              bas.ba_id = bal.bol_ba_id
              AND
              bal.bol_obj_id = obj.obj_id
              AND
              obj.obj_id = ex.it_obj_id) x1,
              (SELECT
              bas.ba_name as ba,
              obj.obj_name as f,
              CASE WHEN obj.obj_ext_owner IS NOT NULL
              THEN obj.obj_ext_owner || '.' || obj.sobj_ext_table
              ELSE NULL
              END as "Table",
              ex.it_heading as "Element",
              CASE
              WHEN obj.obj_ext_owner IS NOT NULL AND ex.it_ext_column IS NOT NULL
              THEN obj.obj_ext_owner || '.' || obj.sobj_ext_table || '.' || ex.it_ext_column
              ELSE NULL
              END as col,
              ex.it_ext_column,
              ex.exp_type,
              ex.exp_id,
              ex.exp_formula1 as exp_formula1
              FROM
              eul5_bas bas,
              eul5_ba_obj_links bal,
              eul5_objs obj,
              eul5_expressions ex
              WHERE
              bas.ba_id = bal.bol_ba_id
              AND
              bal.bol_obj_id = obj.obj_id
              AND
              obj.obj_id = ex.it_obj_id) x2
              WHERE
              JOIN_LINK.JP_KEY_ID = KEYS.KEY_ID
              and JOIN_LINK.EXP_TYPE = 'JP'
              and MST_ITEM.exp_id =
              TO_NUMBER(SUBSTR(JOIN_LINK.EXP_FORMULA1,INSTR(JOIN_LINK.EXP_FORMULA1,'6,')+2,(INSTR(JOIN_LINK.EXP_FORMULA1,'],') - (INSTR(JOIN_LINK.EXP_FORMULA1,'6,')+2))))
              and DTL_ITEM.exp_id =
              TO_NUMBER(SUBSTR(JOIN_LINK.EXP_FORMULA1,INSTR(JOIN_LINK.EXP_FORMULA1,',[6')+4,(INSTR(JOIN_LINK.EXP_FORMULA1,'])') - (INSTR(JOIN_LINK.EXP_FORMULA1,',[6')+4))))
              and MST_ITEM.IT_OBJ_ID = MST_FOLD.OBJ_ID
              and DTL_ITEM.IT_OBJ_ID = DTL_FOLD.OBJ_ID
              and MST_ITEM.exp_id = x1.exp_id (+)
              and DTL_ITEM.exp_id = x2.exp_id (+)
              order by
              x1.ba ,
              MST_FOLD.OBJ_NAME ,
              MST_ITEM.EXP_NAME ,
              x1.col ,
              x2.ba,
              DTL_FOLD.OBJ_NAME ,
              DTL_ITEM.EXP_NAME ,
              x2.col;




              Here I have also the real table name, but somehow there are some joins where I have a MST_ITEM.EXP_NAME Master_Item but no x1.col as "Master_Column" (and same for details). What could this be? A join pointing to a folder item which no longer exists (or event the whole folder is gone?)?