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');
update
For example:-
IF NE_LENGTH value is 3 from 1st query and NE_LENGTH from second query is 2 then it should minus and show as 1
Although "minus" represents subtraction, in piece of code you posted it represents as set operator which returns all rows from the 1st table that don't exist in the 2nd table. Certainly, that's just a general description and there's more behind it, such as (what you already know) the fact select statements have to have column lists that match in number and datatype.
As you literally want to subtract two numbers, that's arithmetic operation and it is the - you need. How to do that? Switch to CTEs (or inline views), join rows on common column(s) and subtract lengths.
Simplified (presuming that span_id is column you can join rows on):
with
first_query as (select span_id, ug_length from ...),
second_query as (select span_id, ne_lwngth from ...)
select a.span_id,
a.ug_length - b.ne_length as difference
from first_query a join second_query b on a.span_id = b.span_id;
Or, using code you posted (I didn't remove anything; looks like you might remove columns you don't actually need):
WITH
first_query AS
(
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) ),
second_query AS
(
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')
SELECT a.span_id,
a.ug_length - b.ne_length as difference
FROM first_query a join second_query b ON a.span_id = b.span_id;