根据ODI底层表查询项目结构

    /*ODI项目结构查询 项目-文件夹-（程序包、接口、存储过程）*/
    SELECT *
      FROM (SELECT spjt.project_name
                  ,spjt.project_code
                  ,spjt.i_project
                  ,sfld.folder_name
                  ,sfld.i_folder
                  ,(SELECT wm_concat(sth.full_text)
                      FROM snp_txt_header sth, snp_obj_state sos, snp_object sob
                     WHERE sth.i_txt_orig = 123
                       AND sth.i_txt = sos.i_txt_memo
                       AND sos.i_instance = sfld.i_folder
                       AND sob.i_objects = sos.i_object
                       AND sob.int_java_name =
                           'com.sunopsis.dwg.dbobj.SnpFolder') AS txt_folder
                  ,spck.pack_name AS object_name
                  ,spck.i_package AS object_id
                  ,'1程序包' AS object_type
              FROM snp_project spjt, snp_folder sfld, snp_package spck
             WHERE 1 = 1
               AND spjt.i_project = sfld.i_project
               AND spck.i_folder = sfld.i_folder
            UNION ALL
            SELECT spjt.project_name
                  ,spjt.project_code
                  ,spjt.i_project
                  ,sfld.folder_name
                  ,sfld.i_folder
                  ,(SELECT wm_concat(sth.full_text)
                      FROM snp_txt_header sth, snp_obj_state sos, snp_object sob
                     WHERE sth.i_txt_orig = 123
                       AND sth.i_txt = sos.i_txt_memo
                       AND sos.i_instance = sfld.i_folder
                       AND sob.i_objects = sos.i_object
                       AND sob.int_java_name =
                           'com.sunopsis.dwg.dbobj.SnpFolder') AS txt_folder
                  ,spp.pop_name AS object_name
                  ,spp.i_pop AS object_id
                  ,'2接口' AS object_type
              FROM snp_project spjt, snp_folder sfld, snp_pop spp
             WHERE 1 = 1
               AND spjt.i_project = sfld.i_project
               AND spp.i_folder = sfld.i_folder
            UNION ALL
            SELECT spjt.project_name
                  ,spjt.project_code
                  ,spjt.i_project
                  ,sfld.folder_name
                  ,sfld.i_folder
                  ,(SELECT wm_concat(sth.full_text)
                      FROM snp_txt_header sth, snp_obj_state sos, snp_object sob
                     WHERE sth.i_txt_orig = 123
                       AND sth.i_txt = sos.i_txt_memo
                       AND sos.i_instance = sfld.i_folder
                       AND sob.i_objects = sos.i_object
                       AND sob.int_java_name =
                           'com.sunopsis.dwg.dbobj.SnpFolder') AS txt_folder
                  ,stt.trt_name AS object_name
                  ,stt.i_trt AS object_id
                  ,'3存储过程' AS object_type
              FROM snp_project spjt, snp_folder sfld, snp_trt stt
             WHERE 1 = 1
               AND spjt.i_project = sfld.i_project
               AND stt.i_folder = sfld.i_folder
               AND stt.trt_type='U')
     ORDER BY project_name, folder_name, object_type, object_name;
    
    /*基础表*/
    --项目
    SELECT * FROM snp_project spjt;
    --文件夹
    SELECT * FROM snp_folder sfld;
    --程序包
    SELECT * FROM snp_package spck;
    --接口
    SELECT * FROM snp_pop spp;
    --存储过程
    SELECT * FROM snp_trt stt WHERE 1=1 AND stt.trt_type='U';