Combining Three Tables and Displaying Their Status (Only Recent Entries for Shared Fields) in Oracle

Problem Statement

The problem at hand is to combine three tables, specifically SEC_OBJECTS_FILLING, SEC_OBJECT_SEALING, and SEC_OBJECT_UNPLUG, and display their status. The twist here is to show only the latest entries for shared fields among these tables.

For instance, consider we have sealed objects (1,2,3,) and sealed objects (1,2,3), but then sealed objects (1,2) again. Object 4 was not sealed. We want to display only the latest activity with our object, not the whole story.

Analyzing the Existing Query

The provided query seems to be a good starting point, but we can improve it further to meet the exact requirements.

SELECT NAME_OBJ, DATA_AREA, SEALS, DATA, NAME_UR,
       SEAL_NUMBER, NAME_USR, DATA_UNPLUG
  FROM
  (
   SELECT sof.NAME_OBJ, sof.DATA_AREA, sof.SEALS, sos.DATA, sos.NAME_UR,
          sos.SEAL_NUMBER, sou.NAME_USR, sou.DATA_UNPLUG,
          RANK() OVER (PARTITION BY sof.NAME_OBJ ORDER BY sos.SEAL_NUMBER DESC) as rnk
     FROM SEC_OBJECTS_FILLING sof
     LEFT JOIN SEC_OBJECT_SEALING sos 
       ON sof.NAME_OBJ = sos.SEALING_OBJECT 
      AND sof.DATA_AREA = sos.DATA_AREA
     LEFT JOIN SEC_OBJECT_UNPLUG sou
       ON sou.OBJECT_UNPLUG = sos.SEALING_OBJECT 
      AND sos.DATA_AREA = sou.DATA_AREA 
      AND sou.SEAL_NUMBER = sos.SEAL_NUMBER
   )
  WHERE rnk = 1;

Improving the Existing Query

However, there are a few potential issues with this query:

• The RANK() function is being used to rank the rows based on SEAL_NUMBER in descending order. However, this might not be exactly what we want because it also includes rows from other tables that have the same NAME_OBJ. We need to filter out those rows.
• Even after filtering the rows using RANK(), if there are multiple rows with the same NAME_OBJ but different SEAL_NUMBER, they will still be included in the results. We should only include one row for each NAME_OBJ.

Let’s improve the query by addressing these issues.

Solution

We can use a subquery to filter out rows that don’t have the latest SEAL_NUMBER. Here is an improved version of the query:

SELECT NAME_OBJ, DATA_AREA, SEALS, DATA, NAME_UR,
       SEAL_NUMBER, NAME_USR, DATA_UNPLUG
  FROM (
   SELECT sof.NAME_OBJ, sof.DATA_AREA, sof.SEALS, sos.DATA, sos.NAME_UR,
          sos.SEAL_NUMBER, sou.NAME_USR, sou.DATA_UNPLUG,
          ROW_NUMBER() OVER (PARTITION BY sof.NAME_OBJ ORDER BY sos.SEAL_NUMBER DESC) as rn
     FROM SEC_OBJECTS_FILLING sof
     LEFT JOIN SEC_OBJECT_SEALING sos 
       ON sof.NAME_OBJ = sos.SEALING_OBJECT 
      AND sof.DATA_AREA = sos.DATA_AREA
     LEFT JOIN SEC_OBJECT_UNPLUG sou
       ON sou.OBJECT_UNPLUG = sos.SEALING_OBJECT 
      AND sos.DATA_AREA = sou.DATA_AREA 
      AND sou.SEAL_NUMBER = sos.SEAL_NUMBER
   )
  WHERE rn = 1;

How the Improved Query Works

Here’s what’s happening in this improved query:

• We’re using a subquery to filter out rows that don’t have the latest SEAL_NUMBER.
• Inside the subquery, we’re using ROW_NUMBER() instead of RANK(). This assigns a unique number to each row within each partition (in this case, NAME_OBJ).
• We’re ordering the rows by SEAL_NUMBER in descending order.
• By only selecting rows where rn = 1, we ensure that we get only one row for each NAME_OBJ.

Example Use Case

Suppose we have three tables: SEC_OBJECTS_FILLING, SEC_OBJECT_SEALING, and SEC_OBJECT_UNPLUG. The first table contains the following data:

The second table contains the following data:

The third table contains the following data:

Running the improved query on these tables will return the following result:

This result shows only the latest entries for shared fields among these tables.

Last modified on 2023-07-29