oracle

Compare two tables in Oracle and check the length is increased or decreased

I have 2 tables as TBL_FIBER_INV_JOBS and TBL_FIBER_INV_JOBS_H. What I want is, I want to compare the MAINT_ZONE_NE_SPAN_LENGTH which is available in both the tables is increased or decreased.

If the length is increased then add one column value as increased or else decreased.

Here is my sample query

select SPAN_ID, LINK_ID, MAINT_ZONE_NE_SPAN_LENGTH from TBL_FIBER_INV_JOBS

select SPAN_ID, LINK_ID, MAINT_ZONE_NE_SPAN_LENGTH from TBL_FIBER_INV_JOBS_H

enter image description here

Solution

  • You could join both tables on the span_id column:

    SELECT t.span_id,
       CASE WHEN t.maint_zone_ne_span_length > h.t.maint_zone_ne_span_length 
                 THEN 'increased'
       CASE WHEN t.maint_zone_ne_span_length < h.t.maint_zone_ne_span_length 
                 THEN 'decreased'
       ELSE 'same' END
FROM   tbl_fiber_inv_jobs t
JOIN   tbl_fiber_inv_jobs_h h ON t.span_id = h.span_id