关闭

使用LISTAGG函数合并行,使用xmltable获取xmlType数据

148人阅读 评论(0) 收藏 举报
分类:
select JIANGLIPUBLICSITUATION.get_area_name(o.area_no) as address,t.zh_title as zh_title,JIANGLIPUBLICSITUATION.get_main_person_info(t.prp_code) as main_person,t.main_org as main_org,extractvalue(pe.prp_xml, ''data/prp_extend/project_profile'') as project_profile,paper.paper_names as paper_names,patent.cnames as cnames,extractvalue(pe.prp_xml, ''data/proposal/extensionapplication'') as application_situation  from proposal t
                      left join proposal_extend pe on pe.prp_code=t.prp_code
                      left join
                      (SELECT prp_code,
                      LISTAGG(paper_name, '';'') WITHIN GROUP(ORDER BY paper_name) AS paper_names
                      FROM (select pe.prp_code as prp_code,t.* from proposal_extend pe,xmltable (''data/represent_papers/represent_paper'' passing pe.prp_xml columns paper_name varchar2(500) path ''paper_name'') t
                      )
                      GROUP BY prp_code) paper on t.prp_code=paper.prp_code
                      left join
                      (SELECT prp_code,
                      LISTAGG(cname, '';'') WITHIN GROUP(ORDER BY cname) AS cnames
                      FROM (select pe.prp_code as prp_code,t.* from proposal_extend pe,xmltable (''data/patents/patent'' passing pe.prp_xml columns cname varchar2(500) path ''cname'') t
                      )
                      GROUP BY prp_code) patent on t.prp_code=patent.prp_code
                      left join organization o on o.org_code=t.recommend_org_code
                      left join organization org on org.org_code=t.org_code
                      left join const_area c on c.area_no=o.area_no
                      left join person p on t.psn_code=p.psn_code
                      left join proposal_cached pc on t.pos_code=pc.pos_code
                      left join org_department od on t.dept_code=od.dept_code

                      where t.grant_code=30 and t.major_review_code is not null



select pe.prp_code as prp_code,t.* from proposal_extend pe,
      xmltable (
        'data/persons/person' passing pe.prp_xml columns
        ranking varchar2(50) path '@seq_no',
        cname varchar2(50) path 'cname',
        prof_title_name varchar2(50) path 'prof_title_name',
        org_name varchar2(100) path 'org_name',
        org_finish_name varchar2(100) path 'org_finish_name',
        work_result_one varchar2(1000) path 'work_result_one'
      ) t
where pe.prp_code=v_prp_code

0
0

猜你在找
【直播】机器学习&数据挖掘7周实训--韦玮
【套餐】系统集成项目管理工程师顺利通关--徐朋
【直播】3小时掌握Docker最佳实战-徐西宁
【套餐】机器学习系列套餐(算法+实战)--唐宇迪
【直播】计算机视觉原理及实战--屈教授
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之矩阵--黄博士
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之凸优化--马博士
【套餐】Javascript 设计模式实战--曾亮
查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:2755次
    • 积分:111
    • 等级:
    • 排名:千里之外
    • 原创:8篇
    • 转载:3篇
    • 译文:0篇
    • 评论:0条
    文章分类