主表:故障单表 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.FAULT_REPORT_STATUS,
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(+)
从表使用GROUP BY外键字段,通过WM_CONCAT函数把报账单号和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.Oracle Database 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,
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.FAULT_REPORT_STATUS,
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 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 (
把需要的连接字符串使用子查询查出来