Select data and insert into another table with multiple conditions in oracle-CodePudding

I have a select statement with multiple conditions which is below

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;

and its data I want to insert in below table which is

Table name:- TBL_FIP_FETCH_NELENGTH

Name         Null Type           
------------ ---- -------------- 
ID                NUMBER(18,8)   
NE_LENGTH         NUMBER         
SPAN_LINK_ID      NVARCHAR2(100)

note rj_span_id and rj_intracity_link_id data can be inserted in one column which is SPAN_LINK_ID

CodePudding user response：

If only one of rj_span_id and rj_intracity_link_id can be not null, then coalesce might be what you're looking for:

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
);

Or, perhaps UNION of two almost same queries - one would insert rj_span_id, and another 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
);

I don't know where is target table's ID column supposed to be populated, though.