Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Due to duplicate value in a link table, values are added twice

BjornlaatJun 26 2023 — edited Jul 3 2023

Hello,

Would like to receive your help with the following issue.
In our transport system OTM, we use 2 tables for the following query. These are the following
SHIPMENT
V30_SHIPMENT_ORDER_RELEASE

The logic is as follows: from the SHIPMENT, the V30_SHIPMENT_ORDER_RELEASE link table is used to look at the sell shipment.

The only challenge lies in the V30_SHIPMENT_ORDER_RELEASE table. a shipment can have several ORDER_RELEASE records. This is required by the system and is a standard solution from Oracle.

The outcome is that we want the cost of the buyshipment with the buyshipment number and the cost of the sellshipment with the sellshipment number.

The current query is as follows:

SELECT
BUY.SHIPMENT_GID buyshipment,
BUY.TOTAL_ACTUAL_COST buy_price,
SELL.SHIPMENT_GID sellshipment,
SELL.TOTAL_ACTUAL_COST sell_price
FROM
SHIPMENT BUY
INNER JOIN V30_SHIPMENT_ORDER_RELEASE V30 ON BUY.SHIPMENT_GID = V30.SHIPMENT_GID
INNER JOIN V30_SHIPMENT_ORDER_RELEASE V300 ON (V30.ORDER_RELEASE_GID = V300.ORDER_RELEASE_GID AND V300.PERSPECTIVE = 'S')
INNER JOIN SHIPMENT SELL ON V300.SHIPMENT_GID = SELL.SHIPMENT_GID
WHERE
BUY.PERSPECTIVE = 'B'

Here is some sample data:

CREATE TABLE SHIPMENT
(
SHIPMENT_GID varchar(255),
PERSPECTIVE varchar(255),
TOTAL_ACTUAL_COST int
);

CREATE TABLE V30_SHIPMENT_ORDER_RELEASE
(
SHIPMENT_GID varchar(255),
PERSPECTIVE varchar(255),
ORDER_RELEASE_GID varchar(255)
);

INSERT INTO SHIPMENT (SHIPMENT_GID, PERSPECTIVE, TOTAL_ACTUAL_COST) VALUES ('RSK.2306230169', 'B', '500');
INSERT INTO SHIPMENT (SHIPMENT_GID, PERSPECTIVE, TOTAL_ACTUAL_COST) VALUES ('RSK.2306230170', 'S', '600');
INSERT INTO SHIPMENT (SHIPMENT_GID, PERSPECTIVE, TOTAL_ACTUAL_COST) VALUES ('RSK.2306230171', 'B', '800');
INSERT INTO SHIPMENT (SHIPMENT_GID, PERSPECTIVE, TOTAL_ACTUAL_COST) VALUES ('RSK.2306230172', 'S', '820');
INSERT INTO V30_SHIPMENT_ORDER_RELEASE (SHIPMENT_GID, PERSPECTIVE, ORDER_RELEASE_GID) VALUES ('RSK.2306230169', 'B', 'RSK.20230623-0093');
INSERT INTO V30_SHIPMENT_ORDER_RELEASE (SHIPMENT_GID, PERSPECTIVE, ORDER_RELEASE_GID) VALUES ('RSK.2306230169', 'B', 'RSK.20230623-0092');
INSERT INTO V30_SHIPMENT_ORDER_RELEASE (SHIPMENT_GID, PERSPECTIVE, ORDER_RELEASE_GID) VALUES ('RSK.2306230170', 'S', 'RSK.20230623-0093');
INSERT INTO V30_SHIPMENT_ORDER_RELEASE (SHIPMENT_GID, PERSPECTIVE, ORDER_RELEASE_GID) VALUES ('RSK.2306230170', 'S', 'RSK.20230623-0092');
INSERT INTO V30_SHIPMENT_ORDER_RELEASE (SHIPMENT_GID, PERSPECTIVE, ORDER_RELEASE_GID) VALUES ('RSK.2306230171', 'B', 'RSK.20230623-0094');
INSERT INTO V30_SHIPMENT_ORDER_RELEASE (SHIPMENT_GID, PERSPECTIVE, ORDER_RELEASE_GID) VALUES ('RSK.2306230172', 'S', 'RSK.20230623-0094');

The result that we should get is:

Thanks in advance

Comments
Post Details
Added on Jun 26 2023
6 comments
97 views