我有 2 个表，我想在其中加入它并获取其数据。例如：表 A 由错误代码组成，其代码描述在表 B 中。
因此，错误代码的值存储一样,1，,2，,1,2表A.
tbl_fiber_invalid_trans_data -> 表 A
TBL_INVALID_ERROR_DATA -> 表 B。
我尝试像下面这样加入，但备注全是空白
SELECT a.SPAN_ID,MAINTENANCE_ZONE_NAME,a.MAINTENANCE_ZONE_CODE,a.R4G_STATE_NAME,
a.NETWORK_CATEGORY,a.NETWORK_TYPE,a.CONSTRUCTION_METHODOLOGY,
a.INVENTORY_STATUS_CODE,a.OWNERSHIP_TYPE_CODE,a.ROUTE_NAME,a.INTRACITY_LINK_ID ,
a.CALCULATED_LENGTH, REPLACE(a.REMARKS, ',1', '1') as REMARKS
FROM tbl_fiber_invalid_trans_data a
left JOIN TBL_INVALID_ERROR_DATA t
ON a.REMARKS = t.ID;让我知道我错在哪里。以及如何替换所有这些逗号分隔值
更新
Table:tbl_fiber_invalid_trans_data
Name Null Type
------------------------ ---- ---------------
SPAN_ID NVARCHAR2(100)
MAINTENANCE_ZONE_NAME NVARCHAR2(100)
MAINTENANCE_ZONE_CODE NVARCHAR2(50)
R4G_STATE_NAME NVARCHAR2(50)
STATE_NAME NVARCHAR2(50)
NETWORK_CATEGORY NVARCHAR2(100)
NETWORK_TYPE NVARCHAR2(100)
CONSTRUCTION_METHODOLOGY NVARCHAR2(50)
INVENTORY_STATUS_CODE NVARCHAR2(20)
OWNERSHIP_TYPE_CODE NVARCHAR2(20)
ROUTE_NAME NVARCHAR2(100)
INTRACITY_LINK_ID NVARCHAR2(100)
CALCULATED_LENGTH NUMBER(38,8)
LAST_UPDATED_BY NVARCHAR2(100)
LAST_UPDATED_DATE DATE
REMARKS NVARCHAR2(1000)
Table:TBL_INVALID_ERROR_DATA
Name Null Type
-------- ---- --------------
ID NUMBER(18,8)
ERR_CODE NVARCHAR2(500) 样本数据如下
表格1
[[图像1] [1]] [1]
示例数据表 2
[![在此处输入图像描述][2]][2]
这是一个简化的示例，展示了如何从逗号分隔列表 ( REMARKSin tbl_fiber_invalid_trans_data) 中提取整数，然后将其加入错误代码列表 ( TBL_INVALID_ERROR_DATA) 以获取消息：
WITH codes AS (
SELECT DISTINCT SPAN_ID, REGEXP_SUBSTR(remarks, '\d+', 1, level) AS code
FROM tbl_fiber_invalid_trans_data
CONNECT BY REGEXP_SUBSTR(remarks, '\d+', 1, level) IS NOT NULL
)
SELECT t1.*, t2.err_code
FROM tbl_fiber_invalid_trans_data t1
JOIN codes c ON c.SPAN_ID = t1.SPAN_ID
LEFT JOIN TBL_INVALID_ERROR_DATA t2 ON t2.id = c.code
ORDER BY t1.SPAN_ID
输出（对于我的简化演示）：
SPAN_ID MAINTENANCE_ZONE_NAME REMARKS ERR_CODE
1 Zone 1 ,1 Span id length too short
2 Zone 2 ,2 Inventory suspended
3 Zone 3 ,1,2 Span id length too short
3 Zone 3 ,1,2 Inventory suspended
4 Zone 4 ,2,1 Span id length too short
4 Zone 4 ,2,1 Inventory suspended
5 Zone 5 null null
如果您希望在一行中包含一个区域的所有错误，您可以将它们聚合到第二个 CTE 中，JOIN然后改为：
WITH codes AS (
SELECT DISTINCT SPAN_ID, REGEXP_SUBSTR(remarks, '\d+', 1, level) AS code
FROM tbl_fiber_invalid_trans_data
CONNECT BY REGEXP_SUBSTR(remarks, '\d+', 1, level) IS NOT NULL
),
msgs AS (
SELECT SPAN_ID, LISTAGG(err_code, ', ') WITHIN GROUP (ORDER BY code) AS err_codes
FROM codes c
LEFT JOIN TBL_INVALID_ERROR_DATA t2 ON t2.id = c.code
GROUP BY SPAN_ID
)
SELECT t1.*, m.err_codes
FROM tbl_fiber_invalid_trans_data t1
JOIN msgs m ON m.SPAN_ID = t1.SPAN_ID
ORDER BY t1.SPAN_ID
输出
SPAN_ID MAINTENANCE_ZONE_NAME REMARKS ERR_CODES
1 Zone 1 ,1 Span id length too short
2 Zone 2 ,2 Inventory suspended
3 Zone 3 ,1,2 Span id length too short, Inventory suspended
4 Zone 4 ,2,1 Span id length too short, Inventory suspended
5 Zone 5 null null
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句