I have 2 queries where I want to subtract NE_LENGTH of 1st query to NE_LENGTH of 2nd query. Same goes for UG_LENGTH & AR_LENGTH columns respectively. I tried like below but I am getting error as Character set mismatch
(SELECT rj_span_id AS span_id,
rj_maintenance_zone_code AS
maint_zone_code,
Round(SUM(Nvl(calculated_length, 0) / 1000), 4) AS
ne_length,
Round(SUM(CASE
WHEN rj_construction_methodology NOT LIKE
'%AERIAL%'
AND rj_construction_methodology NOT LIKE
'%CLAMP%'
OR rj_construction_methodology IS NULL
THEN
Nvl(calculated_length, 0)
ELSE 0
END) / 1000, 4) AS
ug_length,
Round(SUM(CASE
WHEN rj_construction_methodology LIKE
'%AERIAL%'
OR rj_construction_methodology LIKE
'%CLAMP%'
THEN
Nvl(calculated_length, 0)
ELSE 0
END) / 1000, 4) AS
ar_length
FROM ne.mv_span@ne
WHERE Trim(rj_span_id) = 'MHNGAJMHSNGESPN003_BS'
AND inventory_status_code = 'IPL'
AND NOT Regexp_like (Nvl(rj_intracity_link_id, '-'), '_9',
'i')
AND rj_maintenance_zone_code = 'INMHSLPR01'
group by rj_span_id,
rj_maintenance_zone_code)
MINUS
(SELECT SPAN_ID, MAINTENANCEZONECODE as MAINT_ZONE_CODE, maint_zone_ne_span_length as NE_LENGTH,
FSA_UG as UG_LENGTH, FSA_AERIAL as AR_LENGTH
from tbl_fiber_inv_jobs
where SPAN_ID = 'MHNGAJMHSNGESPN003_BS');