新系统改造涉及新旧系统的数据:
需要将
http://10.157.60.21/sgpssc_uds//FileController/downloadFile/sgpssc-1000-20220928-1653-0000083/spgsscApp.jpg,http://10.157.60.21/sgpssc_uds//FileController/downloadFile/sgpssc-1000-20220928-1653-0000081/spgsscApp.jpg
或者
sgpssc-1111-20231017-1353-0000012
或者
sgpssc-1111-20231017-1353-0000013,sgpssc-1111-20231017-1353-0000014
格式转换成[{"index":"sgpssc-1111-20231017-1353-0000012"},{"index":"sgpssc-1111-20231020-0936-0000005"}]格式。为了不影响老系统数据。
SELECT BZYHID,
BZYHMC,
(SELECT VALUE FROM PMCS_EX.T_INTERFACE_CODE WHERE TYPE = 'zzbd_khjb' AND CODE = YHJB) YHJB,
YHJB YHJBID,
ZRW.ZRWMC SSBDRW,
ZRW.ZRWMC BDRW,
ZBX,
ZBY,
DECODE(SFSC, '1', '否', '0', '是') SFSC,
CJR,
CJBM,
TO_CHAR(CJRQ, 'YYYY-MM-DD HH24:MI:SS') CJRQ,
ZRW.ZRW_ID SSBDRW_ID,
BJ.YHDWBM,
BJ.YHDWMC,
BJ.PHOTO
FROM (
SELECT distinct case
when LENGTH(AA.PHOTO) > 0 AND
instr(AA.PHOTO, 'sgpssc-') > 0 THEN '[' || LISTAGG('{"index":"' || AA.PHOTO || '"}', ',') WITHIN GROUP(ORDER BY AA.YHDWMC, AA.PHOTO)
over(partition by AA.BZYHID,
AA.BZYHMC,
AA.YHJB,
AA.SSBDRW,
AA.ZBX,
AA.ZBY,
AA.SFSC,
AA.CJR,
AA.CJBM,
AA.CJRQ,
AA.GIS_ZBX,
AA.GIS_ZBY,
AA.SFZH,
AA.SSBDRW_ID,
AA.YHDWBM,
AA.YHDWMC) || ']'
WHEN LENGTH(AA.PHOTO) = 0 THEN
AA.PHOTO
WHEN instr(AA.PHOTO, 'sgpssc-') < 0 AND
instr(AA.PHOTO, ',') > 0 THEN
'[' || replace(AA.PHOTO, ',', '"},{"index":"') || ']'
END PHOTO,
AA.BZYHID,
AA.BZYHMC,
AA.YHJB,
AA.SSBDRW,
AA.ZBX,
AA.ZBY,
AA.SFSC,
AA.CJR,
AA.CJBM,
AA.CJRQ,
AA.GIS_ZBX,
AA.GIS_ZBY,
AA.SFZH,
AA.SSBDRW_ID,
AA.YHDWBM,
AA.YHDWMC
FROM (SELECT DISTINCT A.PHOTO source_data,
SUBSTR(REGEXP_SUBSTR(A.PHOTO, '[^,]+', 1, LEVEL, 'i'),
INSTR(REGEXP_SUBSTR(A.PHOTO,
'[^,]+',
1,
LEVEL,
'i'),
'sgpssc-'),
33) PHOTO,
A.BZYHID,
A.BZYHMC,
A.YHJB,
A.SSBDRW,
A.ZBX,
A.ZBY,
A.SFSC,
A.CJR,
A.CJBM,
A.CJRQ,
A.GIS_ZBX,
A.GIS_ZBY,
A.SFZH,
A.SSBDRW_ID,
A.YHDWBM,
A.YHDWMC
FROM (SELECT * FROM PMCS_EX.T_ZZGD_BZYH_BJ T WHERE T.SFSC = '0') A
CONNECT BY LEVEL <= LENGTH(A.PHOTO) -
LENGTH(REGEXP_REPLACE(A.PHOTO, ',', '')) + 1) aa
GROUP BY AA.BZYHID,
AA.BZYHMC,
AA.YHJB,
AA.SSBDRW,
AA.ZBX,
AA.ZBY,
AA.SFSC,
AA.CJR,
AA.CJBM,
AA.CJRQ,
AA.GIS_ZBX,
AA.GIS_ZBY,
AA.SFZH,
AA.SSBDRW_ID,
AA.YHDWBM,
AA.YHDWMC,
AA.PHOTO
) BJ
LEFT JOIN PMCS_EX.T_ZZGD_ZRW_BJ ZRW
ON ZRW.ZRW_ID = BJ.SSBDRW_ID
WHERE ZRW.ZRW_ID IS NOT NULL
AND ZRW.SFSX IN ('0', '生效')