使用LISTAGG函数合并行,使用xmltable获取xmlType数据
来源:互联网 发布:wind如何查询行业数据 编辑:程序博客网 时间:2024/06/10 06:27
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
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
- 使用LISTAGG函数合并行,使用xmltable获取xmlType数据
- oracle使用listagg查询电影数据
- Oracle中如何使用REGEXP_SUBSTR,listagg函数
- 使用Listagg分析函数优化wmsys.wm_concat
- Oracle XMLTable 使用教程与实例
- extjs4之合并行插件使用
- Oracle合并行的使用介绍
- 合并行数据
- ORACLE分析函数(6)--使用listagg实现行列转换
- oracle 11g 使用listagg函数合并多行为一行
- oracle xml数据构建 XMLType相关函数
- oracle xml数据构建 XMLType相关函数
- XMLTABLE
- 使用Hibernate4处理Oracle XmlType字段类型
- listagg函数
- LISTAGG函数
- listagg函数
- SQL Server 2005 合并行数据
- AndroidManifest.xml——permission
- 关于jQuery的Validate插件的使用
- Dubbo架构设计详解
- 内存数据库之ApacheDerby
- openproj jvm erron 193
- 使用LISTAGG函数合并行,使用xmltable获取xmlType数据
- UITextView加边框
- yfgxojyp
- 每天一道算法题目(20)——复杂链表的拷贝
- Objective-C NSFileManager 文件管理总结
- C++程序设计 - Week 7 文件操作和模板
- 黑马day14 踢人小案例
- 阿里云ODPS的愿景、技术实现与难点
- CentOS6.5中利用其自带的openjdk设置JAVA_HOME