Hey there! Let's resolve those duplicate rows in your Oracle view. Here's what's happening and how to fix it:

Why Duplicates Are Happening #

Your original view joins the container and document tables. If a single container is linked to multiple documents, the container's details will repeat once for each associated document—this is why you're seeing duplicate rows like the two entries for ID=21, Amount=2.

Solution 1: Use DISTINCT to Remove Exact Duplicates #

The simplest way to eliminate exact duplicate rows is adding the DISTINCT keyword to your SELECT statement. This will keep only unique combinations of your selected columns:

CREATE OR REPLACE FORCE VIEW "GLSID"."VW_GLSID_DOCUMENTS" ("ID", "PARENT_ID", "DOCUMENT_TYPE_ID", "AMOUNT") 
AS 
SELECT DISTINCT 
    container.id, 
    container.container_id AS parent_id, 
    document.document_type_id, 
    container.number_of_documents AS amount 
FROM container 
JOIN document ON document.container_id = container.id;

Solution 2: Use GROUP BY (Alternative for Flexibility) #

If you might need to add aggregation logic later (like summing amounts), using GROUP BY on all your selected columns achieves the same deduplication effect:

CREATE OR REPLACE FORCE VIEW "GLSID"."VW_GLSID_DOCUMENTS" ("ID", "PARENT_ID", "DOCUMENT_TYPE_ID", "AMOUNT") 
AS 
SELECT 
    container.id, 
    container.container_id AS parent_id, 
    document.document_type_id, 
    container.number_of_documents AS amount 
FROM container 
JOIN document ON document.container_id = container.id
GROUP BY 
    container.id, 
    container.container_id, 
    document.document_type_id, 
    container.number_of_documents;

What This Does #

Both approaches will return your desired result set:

ID | PARENT_ID | DOCUMENT_TYPE_ID | Amount
21 | 23 | 3 | 2
21 | 23 | 3 | 1
15 | 26 | 3 | 4

The duplicate row for ID=21, Amount=2 is removed, leaving only unique combinations of your columns.

内容的提问来源于stack exchange，提问作者e2rabi