我有一個帶有多個條件的選擇陳述句，如下所示
select rj_span_id, rj_intracity_link_id,
ROUND (SUM (NVL (calculated_length, 0) / 1000), 4) as NELENGTH
from ne.mv_span@facid147
where rj_network_type IN ('NLD', 'City', 'NLD-City', 'ENT', 'FEEDER', 'FTTx')
and rownum < 900000
and rj_span_id is not null and rj_span_id <> '<Null>'
and rj_intracity_link_id is not null and rj_intracity_link_id <> 'NA' and
rj_intracity_link_id <> '-NA-'
group by rj_span_id, rj_intracity_link_id;
以及我想在下表中插入的資料
Table name:- TBL_FIP_FETCH_NELENGTH
Name Null Type
------------ ---- --------------
ID NUMBER(18,8)
NE_LENGTH NUMBER
SPAN_LINK_ID NVARCHAR2(100)注釋 rj_span_id和rj_intracity_link_id資料可以插入一列中SPAN_LINK_ID
uj5u.com熱心網友回復：
如果只有 rj_span_id 和 rj_intracity_link_id 之一不能為null，那么 coalesce 可能就是您要查找的內容：
insert into target (span_link_id, ne_length)
(
select coalesce(rj_span_id, rj_intracity_link_id)
ROUND (SUM (NVL (calculated_length, 0) / 1000), 4) as NELENGTH
from ne.mv_span@facid147
where rj_network_type IN ('NLD', 'City', 'NLD-City', 'ENT', 'FEEDER', 'FTTx')
and rownum < 900000
and rj_span_id is not null
and rj_span_id <> '<Null>'
and rj_intracity_link_id is not null
and rj_intracity_link_id <> 'NA'
and rj_intracity_link_id <> '-NA-'
group by rj_span_id, rj_intracity_link_id
);
或者，也許UNION是兩個幾乎相同的查詢——一個是 insert rj_span_id，另一個是rj_intracity_link_id。
insert into target (span_link_id, ne_length)
(
select rj_span_id,
ROUND (SUM (NVL (calculated_length, 0) / 1000), 4) as NELENGTH
from ne.mv_span@facid147
where rj_network_type IN ('NLD', 'City', 'NLD-City', 'ENT', 'FEEDER', 'FTTx')
and rownum < 900000
and rj_span_id is not null
and rj_span_id <> '<Null>'
and rj_intracity_link_id is not null
and rj_intracity_link_id <> 'NA'
and rj_intracity_link_id <> '-NA-'
group by rj_span_id, rj_intracity_link_id
union all
select rj_intracity_link_id,
ROUND (SUM (NVL (calculated_length, 0) / 1000), 4) as NELENGTH
from ne.mv_span@facid147
where rj_network_type IN ('NLD', 'City', 'NLD-City', 'ENT', 'FEEDER', 'FTTx')
and rownum < 900000
and rj_span_id is not null
and rj_span_id <> '<Null>'
and rj_intracity_link_id is not null
and rj_intracity_link_id <> 'NA'
and rj_intracity_link_id <> '-NA-'
group by rj_span_id, rj_intracity_link_id
);
不過，我不知道ID應該在哪里填充目標表的列。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/426872.html
標籤：甲骨文
上一篇：如何查詢每個應用的最新版本？