大佬教程收集整理的这篇文章主要介绍了oracle wm_concat函数简单实例,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
主表:故障单表SP_PD_FAulT
从表:报障单表SP_PD_FAulT_REPORT
目的:通过故障单关联查询出该故障单的报障单号,且用逗号分隔显示为一列
SELECT A.*,B.FAulT_NOS,B.FAulT_REPORT_IDS FROM (SELECT * FROM (SELECT disTINCT (F.ID) F_ID,F.*,O.FAulT_OCCUR_TIME,O.FAulT_ADDRESS,O.FAulT_DESC FROM LCAM_SC.SP_PD_FAulT F,(SELECT A.FAulT_ID,A.FeedER_ID,A.PROVINCE_CODE,A.FAulT_REPORT_STATUS,A.FAulT_REPORT_FROM,A.FAulT_REPORT_NO,A.FAulT_ADDRESS,A.FeedER_name,A.SUBSTATION_ID,A.SUBSTATION_name,A.ORGANIZATION_ID,A.TEAM_ID,A.BUREAU_CODE,A.CS_WORKORDER_NO,A.FAulT_DESC,A.FAulT_REPORTER,B.FAulT_OCCUR_TIME FROM LCAM_SC.SP_PD_FAulT_REPORT A,(SELECT FAulT_ID,MIN(FAulT_OCCUR_TIME) FAulT_OCCUR_TIME FROM LCAM_SC.SP_PD_FAulT_REPORT GROUP BY FAulT_ID) B WHERE A.FAulT_ID = B.FAulT_ID AND (A.IS_MERGE = '1' OR A.IS_MERGE IS NulL) AND A.PROVINCE_CODE = '03' AND B.FAulT_OCCUR_TIME BETWEEN TO_DATE('2016-01-01 00:00','yyyy-mm-dd hh24:mi') AND (TO_DATE('2017-08-02 00:00','yyyy-mm-dd hh24:mi'))) O WHERE F.ID = O.FAulT_ID AND F.PROVINCE_CODE IS NOT NulL AND F.BUREAU_CODE IS NOT NulL AND O.PROVINCE_CODE = '03' AND F.FAulT_STATUS IN (2,3,4,5,6,7) AND O.FAulT_OCCUR_TIME BETWEEN TO_DATE('2016-01-01 00:00','yyyy-mm-dd hh24:mi') AND TO_DATE('2017-08-02 00:00','yyyy-mm-dd hh24:mi'))) A,(SELECT TO_CHAR(WM_CONCAT(FAulT_REPORT_NO)) FAulT_NOS,TO_CHAR(WM_CONCAT(ID)) FAulT_REPORT_IDS,FAulT_ID FROM LCAM_SC.SP_PD_FAulT_REPORT GROUP BY FAulT_ID) B WHERE A.ID = B.FAulT_ID(+)
(SELECT TO_CHAR(WM_CONCAT(FAulT_REPORT_NO)) FAulT_NOS,
TO_CHAR(WM_CONCAT(ID)) FAulT_REPORT_IDS,
FAulT_ID
FROM LCAM_SC.SP_PD_FAulT_REPORT
GROUP BY FAulT_ID) B
WHERE A.ID = B.FAulT_ID(+)
但是:报错不存在的LOB值
出现这个错误很多时候是使用了wmsys.wm_concat的同时使用了group,distinct 或者union,本来两者没有问题,问题在于:
1.OracleDatabase 10g Enterprise Edition Release 10.2.0.5.0以后的版本wmsys.wm_concat查询出的是LOB类型
2.oralce的SQL语句中若查询了LOB字段是不能使用distinct,union,和group by等关键字的。
改成在把关联查询写成子查询
SELECT A.*,(SELECT TO_CHAR(WM_CONCAT(FAulT_REPORT_NO)) FROM LCAM_SC.SP_PD_FAulT_REPORT B WHERE A.ID = B.FAulT_ID) FAulT_NOS,(SELECT TO_CHAR(WM_CONCAT(ID)) FROM LCAM_SC.SP_PD_FAulT_REPORT B WHERE A.ID = B.FAulT_ID) FAulT_REPORT_IDS FROM (SELECT disTINCT (F.ID) F_ID,'yyyy-mm-dd hh24:mi')) A
外面包一层
SELECT A.*,
(SELECT TO_CHAR(WM_CONCAT(FAulT_REPORT_NO))
FROM LCAM_SC.SP_PD_FAulT_REPORT B
WHERE A.ID = B.FAulT_ID) FAulT_NOS,
(SELECT TO_CHAR(WM_CONCAT(ID))
FROM LCAM_SC.SP_PD_FAulT_REPORT B
WHERE A.ID = B.FAulT_ID) FAulT_REPORT_IDS
FROM (
把需要的连接字符串使用子查询查出来
以上是大佬教程为你收集整理的oracle wm_concat函数简单实例全部内容,希望文章能够帮你解决oracle wm_concat函数简单实例所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。