create table fuqiang as
select objectid,max(contentCode) as contentCode ,max(name_lang1) as name_lang1 ,max(externalid)as externalid,max(areacode) as areacode,max(restype) as restype,max(validShowDate) as validShowDate,max(accessright) as accessright ,max(pri) as pri,max(description_lang1) as description_lang1,
max(ownerid) as ownerid ,max(providername) as providername ,max(isSubscribeMsg)as isSubscribeMsg,max(iscommentenable)as iscommentenable,max(isportalsubenable) as isportalsubenable,max(resourcebound) as resourcebound,max(resChildren||' ')as resChildren,max(extproperty||' ') as extproperty,
wmsys.wm_concat(decode(NVL(resTypeValue,''),'',';',resTypeValue)||';') as resTypeValue,max(ftpurl) as ftpurl,max(ftpaccount) as ftpaccount,max(ftppasswd) as ftppasswd
from
(
select
max(med.objectid) as objectid,
max(content.contentcode) as contentCode,
max(med.name_lang1) as name_lang1 ,
max((select g.externalid from mdsp_t_categoryinfo g where g.objectid = (select q.source_id from t_cmp_ref_categorytocontent q where q.target_id = content.objectid))) as externalid ,
max((select s.value from T_CMP_EXT_MEDIACONTENT s where s.name = 'areacode' and s.objectid = content.objectid)) as areacode,
max((select s.value from T_CMP_EXT_MEDIACONTENT s where s.name = 'restype' and s.objectid = content.objectid)) as restype,
max((select s.value from T_CMP_EXT_MEDIACONTENT s where s.name = 'validShowDate' and s.objectid = content.objectid)) as validShowDate,
max((select s.value from T_CMP_EXT_MEDIACONTENT s where s.name = 'accessright' and s.objectid = content.objectid)) as accessright,
max((select s.value from T_CMP_EXT_MEDIACONTENT s where s.name = 'pri' and s.objectid = content.objectid)) as pri,
max(med.description_lang1) as description_lang1 ,
max(content.ownerid) as ownerid ,
max((select s.value from T_CMP_EXT_MEDIACONTENT s where s.name = 'providername' and s.objectid = content.objectid)) as providername,
max((select s.value from T_CMP_EXT_MEDIACONTENT s where s.name = 'isSubscribeMsg' and s.objectid = content.objectid)) as isSubscribeMsg,
max((select s.value from T_CMP_EXT_MEDIACONTENT s where s.name = 'iscommentenable' and s.objectid = content.objectid)) as iscommentenable,
max((select s.value from T_CMP_EXT_MEDIACONTENT s where s.name = 'isportalsubenable' and s.objectid = content.objectid)) as isportalsubenable,
max((select s.value from T_CMP_EXT_MEDIACONTENT s where s.name = 'resourcebound' and s.objectid = content.objectid)) as resourcebound,
null as resChildren ,
null as extproperty,
-- max(resport.source_id) as source_id ,
-- max(resport.target_id) as target_id ,
-- max(med.objectid) as objectid,
-- max(med.description_lang1) as description_lang1,
wmsys.wm_concat(decode(NVL(ext001.value,''),'','""',ext001.value)) as resTypeValue,
max((select s.value from T_CMP_EXT_MEDIACONTENT s where s.name = 'ftpurl' and s.objectid = content.objectid)) as ftpurl,
max((select s.value from T_CMP_EXT_MEDIACONTENT s where s.name = 'ftpaccount' and s.objectid = content.objectid)) as ftpaccount,
max((select s.value from T_CMP_EXT_MEDIACONTENT s where s.name = 'ftppasswd' and s.objectid = content.objectid)) as ftppasswd
from T_CMP_TYPE_MEDIACONTENT med,
t_cmp_ref_resource_PortalType resport,
t_cmp_type_portaltypeinfo portinfo,
t_cmp_ref_portalTypeInfo_Url mm,
t_cmp_ext_resourceurl ext001,
t_cmp_type_content content
where resport.source_id = med.objectid
and portinfo.objectid = resport.target_id
and mm.source_id = resport.target_id
and mm.target_id = ext001.objectid
and ext001.name in('displaymode','biglogo','smalllogo','subtitle','needvisitparam')
and content.objectid = med.objectid
and med.name_lang1 = 'fuqiang001_app'
group by resport.target_id
) hello group by objectid;
分享到:
相关推荐
oraclel_ERP特点_cn01.pptx
oraclel_ERP特点_cn01
Oracle Instant Client_12_1 Downloads 有效解决oracle library is not loaded的问题 有效解决ORA-28547:connection to server failed,probable Oracle Net admin error的问题
十分经典的ORACLEL练习题 十分经典的ORACLEL练习题
mysq,sql,oraclel批处理脚本
sort() 方法用于对数组的元素进行排序。接下来通过本文给大家介绍Sort()函数的多种用法,对sort函数的用法相关知识感兴趣的朋友一起学习
mysq与oraclel修改表结构的区别与各自修改用法,mysql的常用命令
oraclel临时数据库无需安装Oracle,就可以给plsql和navicate for oracle提供服务支持
Oracle sql 性能优化调整 1. 选用适合的ORACLE优化器 ORACLE的优化器共有3种: a. RULE (基于规则) b. COST (基于成本) c....设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,...
网盘文件永久链接 目录 1.Oracle在linux下单机安装rar 2Linux6,5安装0rade-11g-RAC(1rar 3Linux6.5安装0race-1g-RAC(2)rar ...22.Oraclel闪回技术,rar 230rade-rman备份恢复rar 24.Oracle OGG简介.rar
Oracle TimesTen 11.2.2.8.0 for Microsoft Windows x64 (64-bit)
Oracle常用Oracle、函数、存储过程[归类].pdf
没有色卡,所以先根据其他机型编译的,可以试用下!给大家分享下,可以体验下电影调调!
oracle基本命令,适合初学者学习的。 一、ORACLE的启动和关闭 1、在单机环境下 要想启动或关闭ORACLE系统必须首先切换到ORACLE用户,如下 su - oracle a、启动ORACLE系统 oracle>svrmgrl SVRMGR>connect internal ...
oracle免驱动绿色版32位和64位带OraOLEDB驱动,使用详情看文章:https://blog.csdn.net/qq243348167/article/details/120368611
包括solr的所有资源文件文件清单如下: IKAnalyzer.cfg.xml ik-analyzer-solr5-5.x.jar solr-analyzer-ik-5.1.0.jar managed-schema mysql-connector-java-5.1.7-bin.jar solrconfig.xml data-config.xml ...
Linux下Oracle的数据定时自动备份
开发环境:eclipse + tomcat7.0 + oraclel 使用框架:Struts2 + Spring + Hibernate 前端页面:Jquery + jquery-easyui 项目介绍: 本系统在开发过程中采用JSP技术,MVC设计模式。系统运行后,满足公司办公的基本...
附光盘性能调优高可用与监控 》从作者贺春旸的实际工作经验出发 针对生产环境MySQL5 5分别从故障处理 高可用 性能优化等角度系统地阐述了数据库运维过程中需要注意的地方及解决问题的思路 并针对2013年2月Oraclel自...
主要介绍了oracle导入导出数据的二种方法,利用PL/SQL Developer工具导出和利用cmd的操作命令导出的出方法,大家参考使用吧